﻿using AutoMapper;
using Dapper;
using Hangfire;
using Hangfire.Common;
using housingsafety.server.Caching.Core;
using housingsafety.server.Entities;
using housingsafety.server.Entities.DataModels;
using housingsafety.server.Helpers;
using housingsafety.server.Models;
using housingsafety.server.Repository;
using housingsafety.server.Svcs;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;
using Newtonsoft.Json.Linq;
using Npgsql;
using Quartz;
using System;
using System.Collections.Generic;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace housingsafety.server.Controllers
{
    [AllowAnonymous]
    public class HangfireController
    {
        private readonly ICache _cache;
        private readonly IMapper _mapper;
        private readonly ZmDbContext _dbContext;
        private readonly IRepository<ZmUser> _userRepository;
        private readonly IRepository<ZmBuildingsLayer> _buildingsLayerRepository;
        private readonly IRepository<ZmPlaceCommercial> _placeCommercialRepository;
        private readonly IRepository<ZmPlaceResident> _placeResidentRepository;
        private readonly IRepository<ZmDepartment> _departmentRepository;
        private readonly IRepository<ZmPlaceResidentCheck> _placeResidentCheckRepository;
        private readonly IRepository<ZmPlaceCommercialCheck> _placeCommercialCheckRepository;
        private readonly ISchedulerFactory _schedulerFactory;
        private readonly Microsoft.AspNetCore.Http.Features.FormOptions _defaultFormOptions = new Microsoft.AspNetCore.Http.Features.FormOptions();
        private readonly string[] _permittedExtensions = { ".mp3", ".mp4", ".jpg", ".jpeg", ".png", ".geojson", ".doc", ".docx" };
        private static object _lock = new object();
        private object p;
        public HangfireController(IRepository<ZmDepartment> departmentRepository
            , IRepository<ZmUser> userRepository
            , IRepository<ZmBuildingsLayer> buildingsLayerRepository
            , IRepository<ZmPlaceCommercial> placeCommercialRepository
            , IRepository<ZmPlaceResident> placeResidentRepository
            , IRepository<ZmPlaceResidentCheck> placeResidentCheckRepository
            , IRepository<ZmPlaceCommercialCheck> placeCommercialCheckRepository
            , IMapper mapper, ICacheProvider cacheProvider, ZmDbContext dbContext
            , ILogger<HangfireController> logger,
            ISchedulerFactory schedulerFactory
            , IRepository<ZmSystemLog> systemLogRepository)
        {
            _mapper = mapper;
            _dbContext = dbContext;
            _cache = cacheProvider.CreateCache();
            _userRepository = userRepository;
            _buildingsLayerRepository = buildingsLayerRepository;
            _placeCommercialRepository = placeCommercialRepository;
            _placeResidentRepository = placeResidentRepository;
            _departmentRepository = departmentRepository;
            _placeResidentCheckRepository = placeResidentCheckRepository;
            _placeCommercialCheckRepository = placeCommercialCheckRepository;
            _schedulerFactory = schedulerFactory;
        }

          public HangfireController()
        {

        }

        [AllowAnonymous]
        [HttpGet("Hangfire/StatHiddenDangerInfo")]
        [Produces("application/json")]
            public RespRet<List<PcYhStatDto>> StatHiddenDangerInfo([FromQuery] string type, [FromQuery] bool reCount = false)
            {
            reCount = true;
                var userNumber = "1380195087291650048";
                if (string.IsNullOrEmpty(userNumber))
                {
                    return new RespRet<List<PcYhStatDto>>(false, "user not logined");
                }

                var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
                if (null == user)
                {
                    return new RespRet<List<PcYhStatDto>>(false, "用户名不存在");
                }

                var curuserDto = _mapper.Map<UserDto>(user);
                if (curuserDto.UserGroup != UserGroupType.FireControl
                    && curuserDto.Role != PermissionRoleType.Admin
                    && curuserDto.Role != PermissionRoleType.City
                    && curuserDto.Role != PermissionRoleType.Street)
                {
                    return new RespRet<List<PcYhStatDto>>(false, "Not stat access");
                }


                DateTime xf_fgcount = Convert.ToDateTime("2023-03-15");
                var cacheData = _cache.Get<List<PcYhStatDto>>("PcYhStatData");

                if (cacheData != null && !reCount)
                {//走缓存

                    var list107 = _departmentRepository.Query().Where(_ => (_.Check107 == 1)).ToList();

                    var list777 = _departmentRepository.Query().Where(_ => (_.Check777 == 1)).ToList();
                    var query107 = from cache in cacheData
                                   join dept in list107 on cache.villageCode equals dept.RegionCode
                                   select new PcYhStatDto
                                   {
                                       villageCode = cache.villageCode,
                                       TownCode = cache.TownCode,
                                       village = cache.village,
                                       Code = cache.Code
                                    ,
                                       createdate = cache.createdate,
                                       Fxyhcount = cache.Fxyhcount,
                                       Name = cache.Name,
                                       Syyhcount = cache.Syyhcount,
                                       Syyhcount_pc = cache.Syyhcount_pc
                                   ,
                                       Town = cache.Town,
                                       Xcyhcount = cache.Xcyhcount,
                                       Ypccount_ch = cache.Ypccount_ch,
                                       Ypccount_ch_yh = cache.Ypccount_ch_yh,
                                       Ypcfwcount = cache.Ypcfwcount,
                                       Ypcfwcount_yh = cache.Ypcfwcount_yh
                                   };
                    var query777 = from cache in cacheData
                                   join dept in list777 on cache.villageCode equals dept.RegionCode
                                   select new PcYhStatDto
                                   {
                                       villageCode = cache.villageCode,
                                       TownCode = cache.TownCode,
                                       village = cache.village,
                                       Code = cache.Code
                                    ,
                                       createdate = cache.createdate,
                                       Fxyhcount = cache.Fxyhcount,
                                       Name = cache.Name,
                                       Syyhcount = cache.Syyhcount,
                                       Syyhcount_pc = cache.Syyhcount_pc
                                   ,
                                       Town = cache.Town,
                                       Xcyhcount = cache.Xcyhcount,
                                       Ypccount_ch = cache.Ypccount_ch,
                                       Ypccount_ch_yh = cache.Ypccount_ch_yh,
                                       Ypcfwcount = cache.Ypcfwcount,
                                       Ypcfwcount_yh = cache.Ypcfwcount_yh
                                   };
                    if (type == "777")
                    {
                        cacheData = query777.ToList();
                    }
                    else if (type == "107")
                    {
                        cacheData = query107.ToList();
                    }


                    var result = new List<PcYhStatDto>();
                    //市级管理员只看到区数据
                    if (user.RoleType == 2 && user.Group == 2)
                    {
                        JObject obj = JObject.Parse(user.District);
                        string RegionCode = (string)obj["RegionCode"]; // "John"
                        result = cacheData.GroupBy(o => o.Code)
                       .Select(g => new PcYhStatDto
                       {
                           Code = g.Key,
                           Syyhcount = g.Sum(o => o.Syyhcount),
                           Syyhcount_pc = g.Sum(o => o.Syyhcount_pc),
                           Name = g.Max(o => o.Name),
                           Xcyhcount = g.Sum(o => o.Xcyhcount),
                           Ypccount_ch = g.Sum(o => o.Ypccount_ch),
                           Ypccount_ch_yh = g.Sum(o => o.Ypccount_ch_yh),
                           Ypcfwcount = g.Sum(o => o.Ypcfwcount),
                           Ypcfwcount_yh = g.Sum(o => o.Ypcfwcount_yh),
                           Fxyhcount = g.Sum(o => o.Fxyhcount),
                           createdate = g.Max(o => o.createdate)
                       }).ToList();
                        result = result.OrderBy(c => c.Code).ToList();
                        return new RespRet<List<PcYhStatDto>>(true, "Ok", result);
                    }
                    else if (user.RoleType == 3 && user.Group == 2)
                    {
                        JObject obj = JObject.Parse(user.District);
                        string RegionCode = (string)obj["RegionCode"]; // "John"
                        cacheData = cacheData.Where(c => c.Code == RegionCode).ToList();
                        result = cacheData.GroupBy(o => o.TownCode)
                      .Select(g => new PcYhStatDto
                      {
                          Code = g.Key,
                          Syyhcount = g.Sum(o => o.Syyhcount),
                          Syyhcount_pc = g.Sum(o => o.Syyhcount_pc),
                          Name = g.Max(o => o.Name),
                          Xcyhcount = g.Sum(o => o.Xcyhcount),
                          Ypccount_ch = g.Sum(o => o.Ypccount_ch),
                          Ypccount_ch_yh = g.Sum(o => o.Ypccount_ch_yh),
                          Ypcfwcount = g.Sum(o => o.Ypcfwcount),
                          Ypcfwcount_yh = g.Sum(o => o.Ypcfwcount_yh),
                          Fxyhcount = g.Sum(o => o.Fxyhcount),
                          createdate = g.Max(o => o.createdate),
                          Town = g.Max(o => o.Town),
                          TownCode = g.Max(o => o.TownCode)
                      }).ToList();
                        result = result.OrderBy(c => c.Code).ToList();
                        return new RespRet<List<PcYhStatDto>>(true, "Ok", result);
                    }

                    else if (user.RoleType == 4 && user.Group == 2)
                    {
                        JObject obj = JObject.Parse(user.District);
                        string RegionCode = (string)obj["RegionCode"]; // "John"
                        cacheData = cacheData.Where(c => c.TownCode == RegionCode).ToList();
                        result = cacheData.GroupBy(o => o.villageCode)
                      .Select(g => new PcYhStatDto
                      {
                          Code = g.Key,
                          Syyhcount = g.Sum(o => o.Syyhcount),
                          Syyhcount_pc = g.Sum(o => o.Syyhcount_pc),
                          Name = g.Max(o => o.Name),
                          Xcyhcount = g.Sum(o => o.Xcyhcount),
                          Ypccount_ch = g.Sum(o => o.Ypccount_ch),
                          Ypccount_ch_yh = g.Sum(o => o.Ypccount_ch_yh),
                          Ypcfwcount = g.Sum(o => o.Ypcfwcount),
                          Ypcfwcount_yh = g.Sum(o => o.Ypcfwcount_yh),
                          Fxyhcount = g.Sum(o => o.Fxyhcount),
                          createdate = g.Max(o => o.createdate),
                          Town = g.Max(o => o.Town),
                          village = g.Max(o => o.village),
                          villageCode = g.Max(o => o.villageCode),
                          TownCode = g.Max(o => o.TownCode)
                      }).ToList();
                        result = result.OrderBy(c => c.Code).ToList();
                        return new RespRet<List<PcYhStatDto>>(true, "Ok", result);
                    }
                    cacheData = result.OrderBy(c => c.Code).ToList();
                    return new RespRet<List<PcYhStatDto>>(true, "Ok", cacheData);
                }
                else
                {//重新计算
                 //var houses = _buildingsLayerRepository.Query()?.ToList();
                 //var houses = _buildingsLayerRepository.Query().Where(_ => (_.Id == "007195f91369475795ed635338bc311f")).ToList();
                    List<ZmBuildingsLayer> houses = new List<ZmBuildingsLayer>();
                    //消防数据时间分割数据池统计
                    //if (dfg == "1")
                    //{

                    //    houses=_buildingsLayerRepository.Query().Where(_ => _.IsDelete != 1 && (_.CreateOn >= xf_fgcount || _.ModifiedOn >= xf_fgcount)).ToList();
                    //}
                    //else if (dfg == "0")
                    //{
                    //    houses = _buildingsLayerRepository.Query().Where(_ => _.IsDelete != 1 && (_.CreateOn >= xf_fgcount || _.ModifiedOn >= xf_fgcount)).ToList();
                    //}
                    //else
                    //{D:\0Code\00三合一\code\2023_svn\housingsafety.server\AutoMapperProfiles.cs

                    houses = _buildingsLayerRepository.Query().Where(_ => (_.IsDelete != 1)).ToList();
                    // houses = _buildingsLayerRepository.Query().Where(_ => (_.IsDelete != 1) && _.Village == "110108027206").ToList();
                    //}

                    // var houses = _buildingsLayerRepository.Query().Where(_ => (_.Id == "0000b90ec62b4419bf0c7c769bbf35b0")).ToList();
                    if (houses == null || houses.Count < 1)
                    {
                        return new RespRet<List<PcYhStatDto>>(false, "no house data");
                    }
                    var dictHouse = new Dictionary<string, List<string>>();
                    var dictHouse_count = new Dictionary<string, int>();
                    var dictHouse_count_yh = new Dictionary<string, int>();
                    var dictHouse_count_yh_cs = new Dictionary<string, int>();
                    foreach (var item in houses)
                    {



                        if (dictHouse.ContainsKey(item.Village))
                        {
                            dictHouse[item.Village].Add(item.Id);
                        }
                        else
                        {
                            //行政区代码  房屋id
                            var list = new List<string>();
                            list.Add(item.Id);
                            dictHouse.Add(item.Village, list);
                            //消隐已排查房屋
                            int getcount = houses.Where(_ => (_.PcztFire == 20 || _.PcztFire == 21) && _.Village == item.Village).Count();
                            dictHouse_count.Add(item.Village, getcount);
                            //消隐已排查房屋有隐患
                            getcount = houses.Where(_ => (_.PcztFire == 21) && _.Village == item.Village).Count();
                            dictHouse_count_yh.Add(item.Village, getcount);

                            //var list = new List<string>();
                            //list.Add(item.Id);
                            //dictHouse.Add(item.Village, list);
                            ////消隐已排查房屋
                            //int getcount = houses.Where(_ => (_.PcztFire == 20 || _.PcztFire == 21) && _.Village == item.Village).Count();
                            //dictHouse_count.Add(item.Village, getcount);
                            ////消隐已排查房屋有隐患
                            //getcount = houses.Where(_ => (_.PcztFire == 21) && _.Village == item.Village).Count();
                            //dictHouse_count_yh.Add(item.Village, getcount);
                        }
                    }

                    var residentDict = new Dictionary<string, PcYhStatDto>();
                    var commercialDict = new Dictionary<string, PcYhStatDto>();

                    int cs_count = 0;
                    int cs_count_yh = 0;
                    int Xcyhcount = 0;//排查隐患



                    #region 居住

                    List<ZmPlaceResident> placeResidentDatas = new List<ZmPlaceResident>();
                    List<ZmPlaceResidentCheck> placeResidentCheckDatas = new List<ZmPlaceResidentCheck>();

                    //消防数据时间分割数据池统计
                    //if (dfg == "1")
                    //{
                    //        placeResidentDatas = _placeResidentRepository
                    //        .Query(_ => _.Type == 0 && _.IsDelete != 1
                    //        && _.Pczt != null && _.Pczt > 1 && (_.CreateOn >= xf_fgcount || _.ModifiedOn >= xf_fgcount))?.ToList();

                    //    placeResidentCheckDatas = _placeResidentCheckRepository.Query(_ => _.Type == 0 )?.ToList();//核查数据
                    //}
                    //else if (dfg == "0")
                    //{
                    //        placeResidentDatas = _placeResidentRepository
                    //        .Query(_ => _.Type == 0 && _.IsDelete != 1
                    //        && _.Pczt != null && _.Pczt > 1 && (_.CreateOn < xf_fgcount || _.ModifiedOn < xf_fgcount))?.ToList(); // 已排查的居住消防场所数据

                    //    placeResidentCheckDatas = _placeResidentCheckRepository.Query(_ => _.Type == 0 )?.ToList();//核查数据
                    //}
                    //else
                    {
                        placeResidentDatas = _placeResidentRepository
                            .Query(_ => _.Type == 0 && _.IsDelete != 1
                            && _.Pczt != null && _.Pczt > 1)?.ToList(); // 已排查的居住消防场所数据

                        placeResidentCheckDatas = _placeResidentCheckRepository.Query(_ => _.Type == 0)?.ToList();//核查数据
                    }


                    if (placeResidentDatas != null && placeResidentDatas.Count > 0)
                    {
                        var pHIds = placeResidentDatas.Select(_ => _.HouseId).Distinct().ToList();
                        if (placeResidentCheckDatas != null && placeResidentCheckDatas.Count > 0) // 存在核查数据
                        {
                            foreach (var area in dictHouse)
                            {
                                var value = new PcYhStatDto { Code = area.Key };
                                residentDict.Add(area.Key, value);
                                var houseIds = area.Value.Where(_ => pHIds.Contains(_));

                                foreach (var hid in houseIds)
                                {
                                    var findPlaceResidentDatas = placeResidentDatas.FindAll(_ => _.HouseId == hid)?.ToList();
                                    ////排查隐患数量
                                    //int NumyhwtXfcount = (int)findPlaceResidentDatas.Sum(o => o.NumyhwtXf);
                                    //Syyhcount_pc
                                    //dictHouse_count_yh_cs[hid] = NumyhwtXfcount;

                                    foreach (var place in findPlaceResidentDatas)
                                    {
                                        //dictHouse_count_yh_cs.Add(place.Id, (int)place.NumyhwtXf);
                                        cs_count++;
                                        if (place.Pczt == 21)
                                        {
                                            cs_count_yh++;
                                        }
                                        var checkPlaces = placeResidentCheckDatas
                                            .FindAll(_ => _.PlaceId == place.Id)?.OrderBy(_ => _.CreateOn)?.ToList();
                                        if (checkPlaces != null && checkPlaces.Count > 0) // 存在此场所的核查数据
                                        {
                                            var pcData = checkPlaces.Find(_ => string.IsNullOrEmpty(_.CheckMind));
                                            if (pcData != null)
                                            {
                                                var aa = CalcYinHuanNum(pcData);
                                                residentDict[area.Key].Fxyhcount += aa.Item1;

                                                var findCheckDatas = checkPlaces.Where(_ => _.Id != pcData.Id)
                                                    .OrderBy(_ => _.CreateOn).ToList();
                                                for (int i = 0; i < findCheckDatas.Count; i++)
                                                {
                                                    if (i == 0)
                                                    {
                                                        var rst = CalcPlaceResident(pcData, findCheckDatas[i]);
                                                        residentDict[area.Key].Fxyhcount += rst.Fxyhcount;
                                                        residentDict[area.Key].Xcyhcount += rst.Xcyhcount;
                                                    }
                                                    else
                                                    {
                                                        var rst = CalcPlaceResident(findCheckDatas[i - 1], findCheckDatas[i]);
                                                        residentDict[area.Key].Fxyhcount += rst.Fxyhcount;
                                                        residentDict[area.Key].Xcyhcount += rst.Xcyhcount;
                                                    }
                                                }
                                            }
                                        }
                                        else
                                        {
                                            var aa = CalcYinHuanNum(place);
                                            residentDict[area.Key].Fxyhcount += aa.Item1;
                                            //commercialDict[area.Key].Fxyhcount += (int)place.NumyhwtXf;
                                        }
                                    }
                                }
                            }
                        }
                        else// 不存在核查数据
                        {
                            foreach (var area in dictHouse)
                            {
                                var value = new PcYhStatDto { Code = area.Key };
                                residentDict.Add(area.Key, value);
                                var houseIds = area.Value.Where(_ => pHIds.Contains(_));

                                foreach (var hid in houseIds)
                                {
                                    var findPlaceResidentDatas = placeResidentDatas.FindAll(_ => _.HouseId == hid)?.ToList();
                                    foreach (var place in findPlaceResidentDatas)
                                    {
                                        dictHouse_count_yh_cs.Add(place.Id, (int)place.NumyhwtXf);
                                        cs_count++;
                                        if (place.Pczt == 21)
                                        {
                                            cs_count_yh++;
                                        }
                                        var aa = CalcYinHuanNum(place);
                                        residentDict[area.Key].Fxyhcount += aa.Item1;
                                        //commercialDict[area.Key].Fxyhcount += (int)place.NumyhwtXf;
                                    }
                                }
                            }
                        }
                    }

                    #endregion

                    #region 商业




                    List<ZmPlaceCommercial> placeCommercialDatas = new List<ZmPlaceCommercial>();
                    List<ZmPlaceCommercialCheck> placeCommercialCheckDatas = new List<ZmPlaceCommercialCheck>();


                    //消防数据时间分割数据池统计
                    //if (dfg == "1")
                    //{
                    //    placeCommercialDatas = _placeCommercialRepository
                    //    .Query(_ => _.Type == 0 && _.IsDelete != 1
                    //    && _.Pczt != null && _.Pczt > 1 && (_.CreateOn >= xf_fgcount || _.ModifiedOn >= xf_fgcount))?.ToList();
                    //    placeCommercialCheckDatas = _placeCommercialCheckRepository.Query(_ => _.Type == 0)?.ToList();//核查数据
                    //}
                    //else if (dfg == "0")
                    //{
                    //    placeCommercialDatas = _placeCommercialRepository
                    //    .Query(_ => _.Type == 0 && _.IsDelete != 1
                    //    && _.Pczt != null && _.Pczt > 1 && (_.CreateOn < xf_fgcount || _.ModifiedOn < xf_fgcount))?.ToList(); // 已排查的居住消防场所数据
                    //    placeCommercialCheckDatas = _placeCommercialCheckRepository.Query(_ => _.Type == 0)?.ToList();//核查数据
                    //}
                    //else
                    {
                        placeCommercialDatas = _placeCommercialRepository
                            .Query(_ => _.Type == 0 && _.IsDelete != 1
                            && _.Pczt != null && _.Pczt > 1)?.ToList(); // 已排查的商业消防场所数据

                        placeCommercialCheckDatas = _placeCommercialCheckRepository
                        .Query(_ => _.Type == 0)?.ToList();//核查数据

                    }

                    if (placeCommercialDatas != null && placeCommercialDatas.Count > 0)
                    {
                        var pHIds = placeCommercialDatas.Select(_ => _.HouseId).Distinct().ToList();

                        if (placeCommercialCheckDatas != null && placeCommercialCheckDatas.Count > 0) // 存在核查数据
                        {
                            foreach (var area in dictHouse)
                            {
                                var value = new PcYhStatDto { Code = area.Key };
                                commercialDict.Add(area.Key, value);
                                var houseIds = area.Value.Where(_ => pHIds.Contains(_));

                                foreach (var hid in houseIds)
                                {
                                    var findPlaceDatas = placeCommercialDatas.FindAll(_ => _.HouseId == hid)?.ToList();
                                    foreach (var place in findPlaceDatas)
                                    {
                                        cs_count++;
                                        if (place.Pczt == 21)
                                        {
                                            cs_count_yh++;
                                        }
                                        Xcyhcount += (int)place.NumyhwtXf;
                                        var checkPlaces = placeCommercialCheckDatas
                                            .FindAll(_ => _.PlaceId == place.Id)?.OrderBy(_ => _.CreateOn)?.ToList();
                                        if (checkPlaces != null && checkPlaces.Count > 0) // 存在此场所的核查数据
                                        {
                                            var pcData = checkPlaces.Find(_ => string.IsNullOrEmpty(_.CheckMind));
                                            if (pcData != null)
                                            {
                                                var aa = CalcYinHuanNum(pcData);
                                                commercialDict[area.Key].Fxyhcount += aa.Item1;

                                                var findCheckDatas = checkPlaces.Where(_ => _.Id != pcData.Id)
                                                    .OrderBy(_ => _.CreateOn).ToList();
                                                for (int i = 0; i < findCheckDatas.Count; i++)
                                                {
                                                    if (i == 0)
                                                    {
                                                        var rst = CalcPlaceCommercial(pcData, findCheckDatas[i]);
                                                        commercialDict[area.Key].Fxyhcount += rst.Fxyhcount;
                                                        commercialDict[area.Key].Xcyhcount += rst.Xcyhcount;
                                                    }
                                                    else
                                                    {
                                                        var rst = CalcPlaceCommercial(findCheckDatas[i - 1], findCheckDatas[i]);
                                                        commercialDict[area.Key].Fxyhcount += rst.Fxyhcount;
                                                        commercialDict[area.Key].Xcyhcount += rst.Xcyhcount;
                                                    }
                                                }
                                            }
                                        }
                                        else
                                        {
                                            var aa = CalcYinHuanNum(place);
                                            commercialDict[area.Key].Fxyhcount += aa.Item1;
                                            //commercialDict[area.Key].Fxyhcount += (int)place.NumyhwtXf;
                                        }
                                    }
                                }
                            }
                        }
                        else// 不存在核查数据
                        {
                            foreach (var area in dictHouse)
                            {
                                var value = new PcYhStatDto { Code = area.Key };
                                commercialDict.Add(area.Key, value);
                                var houseIds = area.Value.Where(_ => pHIds.Contains(_));

                                foreach (var hid in houseIds)
                                {
                                    var findPlaceDatas = placeCommercialDatas.FindAll(_ => _.HouseId == hid)?.ToList();
                                    foreach (var place in findPlaceDatas)
                                    {
                                        cs_count++;
                                        if (place.Pczt == 21)
                                        {
                                            cs_count_yh++;
                                        }
                                        Xcyhcount += (int)place.NumyhwtXf;
                                        var aa = CalcYinHuanNum(place);
                                        commercialDict[area.Key].Fxyhcount += aa.Item1;
                                        //commercialDict[area.Key].Fxyhcount += (int)place.NumyhwtXf;
                                    }
                                }
                            }
                        }
                    }
                    #endregion

                    var totalDict = new Dictionary<string, PcYhStatDto>();
                    foreach (var item in residentDict)
                    {


                        if (totalDict.ContainsKey(item.Key))
                        {
                            totalDict[item.Key].Fxyhcount += item.Value.Fxyhcount;
                            totalDict[item.Key].Xcyhcount += item.Value.Xcyhcount;

                        }
                        else
                        {
                            var value = new PcYhStatDto { Code = item.Key };
                            value.Fxyhcount = item.Value.Fxyhcount;
                            value.Xcyhcount = item.Value.Xcyhcount;


                            //已排查-房屋
                            var getYpcfwcount = dictHouse_count.Where(q => q.Key == item.Key).FirstOrDefault();
                            value.Ypcfwcount = getYpcfwcount.Value;
                            //消隐-房屋
                            getYpcfwcount = dictHouse_count_yh.Where(q => q.Key == item.Key).FirstOrDefault();
                            value.Ypcfwcount_yh = getYpcfwcount.Value;

                            string sqlxfyh = "select  COALESCE((select sum(aa.numyhwt_xf) from zm_place_commercial  aa,zm_buildings_layer  bb  where  aa.house_id=bb.ID and bb.town='" + item.Key + "'and  aa.is_delete IS NULL AND aa.type = 0 AND aa.pczt > 1 ),0)+ COALESCE((select sum(aa.numyhwt_xf) from zm_place_resident  aa,zm_buildings_layer  bb  where  aa.house_id=bb.ID and bb.town='" + item.Key + "'and  aa.is_delete IS NULL AND aa.type = 0 AND aa.pczt > 1),0) dd ";

                            //if (dfg == "1")
                            //{
                            //    sqlxfyh = "select  COALESCE((select sum(aa.numyhwt_xf) from zm_place_commercial  aa,zm_buildings_layer  bb  where  aa.house_id=bb.ID and bb.town='" + item.Key + "'and  aa.is_delete IS NULL AND aa.type = 0 AND aa.pczt > 1 and (aa.create_on>='" + xf_fgcount + "' or aa.modified_on>='" + xf_fgcount + "') ),0)+ COALESCE((select sum(aa.numyhwt_xf) from zm_place_resident  aa,zm_buildings_layer  bb  where  aa.house_id=bb.ID and bb.town='" + item.Key + "'and  aa.is_delete IS NULL AND aa.type = 0 AND aa.pczt > 1 and (aa.create_on>='" + xf_fgcount + "' or aa.modified_on>='" + xf_fgcount + "')),0) dd ";
                            //}
                            //else if (dfg == "0")
                            //{
                            //    sqlxfyh = "select  COALESCE((select sum(aa.numyhwt_xf) from zm_place_commercial  aa,zm_buildings_layer  bb  where  aa.house_id=bb.ID and bb.town='" + item.Key + "'and  aa.is_delete IS NULL AND aa.type = 0 AND aa.pczt > 1 and (aa.create_on<'" + xf_fgcount + "' or aa.modified_on<'" + xf_fgcount + "') ),0)+ COALESCE((select sum(aa.numyhwt_xf) from zm_place_resident  aa,zm_buildings_layer  bb  where  aa.house_id=bb.ID and bb.town='" + item.Key + "'and  aa.is_delete IS NULL AND aa.type = 0 AND aa.pczt > 1 and (aa.create_on<'" + xf_fgcount + "' or aa.modified_on<'" + xf_fgcount + "')),0) dd ";
                            //}
                            //else
                            {
                                sqlxfyh = "select  COALESCE((select sum(aa.numyhwt_xf) from zm_place_commercial  aa,zm_buildings_layer  bb  where  aa.house_id=bb.ID and bb.town='" + item.Key + "'and  aa.is_delete IS NULL AND aa.type = 0 AND aa.pczt > 1 ),0)+ COALESCE((select sum(aa.numyhwt_xf) from zm_place_resident  aa,zm_buildings_layer  bb  where  aa.house_id=bb.ID and bb.town='" + item.Key + "'and  aa.is_delete IS NULL AND aa.type = 0 AND aa.pczt > 1),0) dd ";
                            }

                            var xfyh_count = _dbContext.Database.GetDbConnection().Query<long>(sqlxfyh).FirstOrDefault();
                            value.Syyhcount_pc = xfyh_count;


                            totalDict.Add(item.Key, value);
                        }
                    }

                    foreach (var item in commercialDict)
                    {
                        if (totalDict.ContainsKey(item.Key))
                        {
                            totalDict[item.Key].Fxyhcount += item.Value.Fxyhcount;
                            totalDict[item.Key].Xcyhcount += item.Value.Xcyhcount;
                        }
                        else
                        {
                            var value = new PcYhStatDto { Code = item.Key };
                            value.Fxyhcount = item.Value.Fxyhcount;
                            value.Xcyhcount = item.Value.Xcyhcount;
                            totalDict.Add(item.Key, value);
                        }
                    }

                    var datas = new List<PcYhStatDto>();
                    foreach (var item in totalDict)
                    {
                        item.Value.Syyhcount = item.Value.Fxyhcount - item.Value.Xcyhcount;
                        datas.Add(item.Value);
                    }

                    var departments = _cache.Get("departments") as List<ZmDepartment>;
                    if (null == departments)
                    {
                        departments = _departmentRepository.Query().ToList();
                        _cache.Set("departments", departments);
                    }

                    foreach (var item in datas)
                    {
                        var find1 = departments.Find(_ => _.RegionCode == item.Code);
                        if (find1 != null)
                        {
                            var findpid = departments.Find(_ => _.RegionCode == find1.ParentId);
                            item.Town = findpid?.RegionName;
                            item.TownCode = findpid?.RegionCode;
                        }
                        item.village = find1?.RegionName;
                        item.villageCode = find1?.RegionCode;
                    }
                    foreach (var item in datas)
                    {
                        var find1 = departments.Find(_ => _.RegionCode == item.TownCode);
                        if (find1 != null)
                        {
                            var findpid = departments.Find(_ => _.RegionCode == find1.ParentId);
                            item.Name = findpid?.RegionName;
                            item.Code = findpid?.RegionCode;
                        }
                    }
                    List<PcYhStatDto> filteredDatas = new List<PcYhStatDto>();
                    foreach (var item in datas)
                    {
                        if (item.Name != null)
                        {
                            filteredDatas.Add(item);
                        }
                    }
                    datas = filteredDatas;
                    // Use filteredDatas collection for further processing


                    //缓存
                    _cache.Set("PcYhStatData", datas);

                    var list107 = _departmentRepository.Query().Where(_ => (_.Check107 == 1)).ToList();

                    var list777 = _departmentRepository.Query().Where(_ => (_.Check777 == 1)).ToList();
                    var query107 = from cache in datas
                                   join dept in list107 on cache.villageCode equals dept.RegionCode
                                   select new PcYhStatDto
                                   {
                                       villageCode = cache.villageCode,
                                       TownCode = cache.TownCode,
                                       village = cache.village,
                                       Code = cache.Code
                                    ,
                                       createdate = cache.createdate,
                                       Fxyhcount = cache.Fxyhcount,
                                       Name = cache.Name,
                                       Syyhcount = cache.Syyhcount,
                                       Syyhcount_pc = cache.Syyhcount_pc
                                   ,
                                       Town = cache.Town,
                                       Xcyhcount = cache.Xcyhcount,
                                       Ypccount_ch = cache.Ypccount_ch,
                                       Ypccount_ch_yh = cache.Ypccount_ch_yh,
                                       Ypcfwcount = cache.Ypcfwcount,
                                       Ypcfwcount_yh = cache.Ypcfwcount_yh
                                   };
                    var query777 = from cache in datas
                                   join dept in list777 on cache.villageCode equals dept.RegionCode
                                   select new PcYhStatDto
                                   {
                                       villageCode = cache.villageCode,
                                       TownCode = cache.TownCode,
                                       village = cache.village,
                                       Code = cache.Code
                                    ,
                                       createdate = cache.createdate,
                                       Fxyhcount = cache.Fxyhcount,
                                       Name = cache.Name,
                                       Syyhcount = cache.Syyhcount,
                                       Syyhcount_pc = cache.Syyhcount_pc
                                   ,
                                       Town = cache.Town,
                                       Xcyhcount = cache.Xcyhcount,
                                       Ypccount_ch = cache.Ypccount_ch,
                                       Ypccount_ch_yh = cache.Ypccount_ch_yh,
                                       Ypcfwcount = cache.Ypcfwcount,
                                       Ypcfwcount_yh = cache.Ypcfwcount_yh
                                   };
                    if (type == "777")
                    {
                        cacheData = query777.ToList();
                    }
                    else if (type == "107")
                    {
                        cacheData = query107.ToList();
                    }

                    //市级管理员只看到区数据
                    if (user.RoleType == 2 && user.Group == 2)
                    {
                        JObject obj = JObject.Parse(user.District);
                        string RegionCode = (string)obj["RegionCode"]; // "John"
                        datas = datas.GroupBy(o => o.Code)
                       .Select(g => new PcYhStatDto
                       {
                           Code = g.Key,
                           Syyhcount = g.Sum(o => o.Syyhcount),
                           Syyhcount_pc = g.Sum(o => o.Syyhcount_pc),
                           Name = g.Max(o => o.Name),
                           Xcyhcount = g.Sum(o => o.Xcyhcount),
                           Ypccount_ch = g.Sum(o => o.Ypccount_ch),
                           Ypccount_ch_yh = g.Sum(o => o.Ypccount_ch_yh),
                           Ypcfwcount = g.Sum(o => o.Ypcfwcount),
                           Ypcfwcount_yh = g.Sum(o => o.Ypcfwcount_yh),
                           Fxyhcount = g.Sum(o => o.Fxyhcount),
                           createdate = g.Max(o => o.createdate)
                       }).ToList();
                        datas = datas.OrderBy(c => c.Code).ToList();
                        return new RespRet<List<PcYhStatDto>>(true, "Ok", datas);
                    }
                    else if (user.RoleType == 3 && user.Group == 2)
                    {
                        JObject obj = JObject.Parse(user.District);
                        string RegionCode = (string)obj["RegionCode"]; // "John"
                        datas = datas.Where(c => c.Code == RegionCode).ToList();
                        datas = datas.GroupBy(o => o.TownCode)
                      .Select(g => new PcYhStatDto
                      {
                          Code = g.Key,
                          Syyhcount = g.Sum(o => o.Syyhcount),
                          Syyhcount_pc = g.Sum(o => o.Syyhcount_pc),
                          Name = g.Max(o => o.Name),
                          Xcyhcount = g.Sum(o => o.Xcyhcount),
                          Ypccount_ch = g.Sum(o => o.Ypccount_ch),
                          Ypccount_ch_yh = g.Sum(o => o.Ypccount_ch_yh),
                          Ypcfwcount = g.Sum(o => o.Ypcfwcount),
                          Ypcfwcount_yh = g.Sum(o => o.Ypcfwcount_yh),
                          Fxyhcount = g.Sum(o => o.Fxyhcount),
                          createdate = g.Max(o => o.createdate),
                          Town = g.Max(o => o.Town),
                          TownCode = g.Max(o => o.TownCode)
                      }).ToList();
                    }
                    else if (user.RoleType == 4 && user.Group == 2)
                    {
                        JObject obj = JObject.Parse(user.District);
                        string RegionCode = (string)obj["RegionCode"]; // "John"
                        datas = datas.Where(c => c.TownCode == RegionCode).ToList();
                        datas = datas.GroupBy(o => o.villageCode)
                      .Select(g => new PcYhStatDto
                      {
                          Code = g.Key,
                          Syyhcount = g.Sum(o => o.Syyhcount),
                          Syyhcount_pc = g.Sum(o => o.Syyhcount_pc),
                          Name = g.Max(o => o.Name),
                          Xcyhcount = g.Sum(o => o.Xcyhcount),
                          Ypccount_ch = g.Sum(o => o.Ypccount_ch),
                          Ypccount_ch_yh = g.Sum(o => o.Ypccount_ch_yh),
                          Ypcfwcount = g.Sum(o => o.Ypcfwcount),
                          Ypcfwcount_yh = g.Sum(o => o.Ypcfwcount_yh),
                          Fxyhcount = g.Sum(o => o.Fxyhcount),
                          createdate = g.Max(o => o.createdate),
                          Town = g.Max(o => o.Town),
                          village = g.Max(o => o.village),
                          villageCode = g.Max(o => o.villageCode),
                          TownCode = g.Max(o => o.TownCode)
                      }).ToList();
                        datas = datas.OrderBy(c => c.Code).ToList();
                        return new RespRet<List<PcYhStatDto>>(true, "Ok", datas);
                    }
                    datas = datas.OrderBy(c => c.Code).ToList();
                    //if (type == "1")
                    //{

                    //    string del = "delete from zm_xf_xy_count316 ";
                    //    foreach (var v in datas)
                    //    {
                    //        var d = Guid.NewGuid().ToString();
                    //        string dd = DateTime.Now.ToString();
                    //        string sql = " INSERT INTO public.zm_xf_xy_count316 " +
                    //            "(id, xzq, xzqcode, town, towncode, village, villagecode, fxyhcount, xcyhcount, syyhcount, ypcfwcount, ypcfwcount_yh, createdate) VALUES" +
                    //            " ('" + d + "', '" + v.Name + "', '" + v.Code + "', '" + v.Town + "', '" + v.TownCode + "', '" + v.village + "', '" + v.villageCode + "', '" + v.Fxyhcount + "', '" + v.Xcyhcount + "', '" + v.Syyhcount + "', '" + v.Ypcfwcount + "', '" + v.Ypcfwcount_yh + "',  '" + dd + "');";
                    //        var num = _dbContext.Database.ExecuteSqlRaw(sql);
                    //    }
                    //}
                    //_cache.Set("Isloading", true);
                    //if (type == "1")
                    //{

                    //    string del = "delete from zm_xf_xy_count316 ";
                    //    foreach (var v in datas)
                    //    {
                    //        var d = Guid.NewGuid().ToString();
                    //        string dd = DateTime.Now.ToString();
                    //        string sql = " INSERT INTO public.zm_xf_xy_count316 " +
                    //            "(id, xzq, xzqcode, town, towncode, village, villagecode, fxyhcount, xcyhcount, syyhcount, ypcfwcount, ypcfwcount_yh, createdate) VALUES" +
                    //            " ('" + d + "', '" + v.Name + "', '" + v.Code + "', '" + v.Town + "', '" + v.TownCode + "', '" + v.village + "', '" + v.villageCode + "', '" + v.Fxyhcount + "', '" + v.Xcyhcount + "', '" + v.Syyhcount + "', '" + v.Ypcfwcount + "', '" + v.Ypcfwcount_yh + "',  '" + dd + "');";
                    //        var num = _dbContext.Database.ExecuteSqlRaw(sql);
                    //    }
                    //}
                    //_cache.Set("Isloading", true);
                    return new RespRet<List<PcYhStatDto>>(true, "Ok", datas);
                }
            }
        private Tuple<int, int> CalcYinHuanNum(ZmPlaceResidentCheck request)
        {

            var numyhwtXf = 0;
            if (request.UseElectricBicycle.HasValue && request.UseElectricBicycle == 1)
            {
                numyhwtXf += 1;
            }

            if (request.UseBottledLiquefiedGas.HasValue && request.UseBottledLiquefiedGas == 1)
            {
                numyhwtXf += 1;
            }

            if (request.UseStackedItems.HasValue && request.UseStackedItems == 1)
            {
                numyhwtXf += 1;
            }

            if (request.UseBurglarMesh.HasValue && request.UseBurglarMesh == 1)
            {
                numyhwtXf += 1;
            }

            if (request.UseCeiling.HasValue && request.UseCeiling == 1)
            {
                numyhwtXf += 1;
            }

            if (request.IsCompletelyIsolated.HasValue && request.IsCompletelyIsolated == 1)
            {
                numyhwtXf += 1;
            }

            if (request.UseFlammableMaterials.HasValue && request.UseFlammableMaterials == 1)
            {
                numyhwtXf += 1;
            }

            if (request.UnauthorizedElectricalConnect.HasValue && request.UnauthorizedElectricalConnect == 1)
            {
                numyhwtXf += 1;
            }

            if (request.ChargingFacilitiesNoStandard.HasValue && request.ChargingFacilitiesNoStandard == 1)
            {
                numyhwtXf += 1;
            }

            if (request.NotEnoughExits.HasValue && request.NotEnoughExits == 1)
            {
                numyhwtXf += 1;
            }

            if (request.UseFlammableInsulationMaterials.HasValue && request.UseFlammableInsulationMaterials == 1)
            {
                numyhwtXf += 1;
            }

            if (request.UseFlammableDangerousGoods.HasValue && request.UseFlammableDangerousGoods == 1)
            {
                numyhwtXf += 1;
            }

            if (request.AboveThreeFloorSelf.HasValue && request.AboveThreeFloorSelf == 0)
            {
                numyhwtXf += 1;
            }

            //if (request.UseIndependentSmokeAlarm.HasValue && request.UseIndependentSmokeAlarm == 0)
            //{
            //    numyhwtXf += 1;
            //}

            //if (request.AboveThreeFloorsEscape.HasValue && request.AboveThreeFloorsEscape == 0)
            //{
            //    numyhwtXf += 1;
            //}

            var level = 2;
            if (numyhwtXf == 0)
            {
                level = 2;
            }
            else if (numyhwtXf >= 1 && numyhwtXf < 4)
            {
                level = 3;
            }
            else if (numyhwtXf >= 4)
            {
                level = 1;
            }

            return new Tuple<int, int>(numyhwtXf, level);
        }
        private PcYhStatDto CalcPlaceResident(ZmPlaceResidentCheck pre, ZmPlaceResidentCheck cur)
        {
            var newRst = new PcYhStatDto();
            if (cur.UseElectricBicycle.HasValue && pre.UseElectricBicycle.HasValue)
            {
                var val = cur.UseElectricBicycle - pre.UseElectricBicycle;
                if (val > 0)
                {
                    newRst.Fxyhcount += 1;
                }
                else if (val < 0)
                {
                    newRst.Xcyhcount += 1;
                }
            }

            if (cur.UseBottledLiquefiedGas.HasValue && pre.UseBottledLiquefiedGas.HasValue)
            {
                var val = cur.UseBottledLiquefiedGas - pre.UseBottledLiquefiedGas;
                if (val > 0)
                {
                    newRst.Fxyhcount += 1;
                }
                else if (val < 0)
                {
                    newRst.Xcyhcount += 1;
                }
            }

            if (cur.UseStackedItems.HasValue && pre.UseStackedItems.HasValue)
            {
                var val = cur.UseStackedItems - pre.UseStackedItems;
                if (val > 0)
                {
                    newRst.Fxyhcount += 1;
                }
                else if (val < 0)
                {
                    newRst.Xcyhcount += 1;
                }
            }

            if (cur.UseBurglarMesh.HasValue && pre.UseBurglarMesh.HasValue)
            {
                var val = cur.UseBurglarMesh - pre.UseBurglarMesh;
                if (val > 0)
                {
                    newRst.Fxyhcount += 1;
                }
                else if (val < 0)
                {
                    newRst.Xcyhcount += 1;
                }
            }

            if (cur.UseCeiling.HasValue && pre.UseCeiling.HasValue)
            {
                var val = cur.UseCeiling - pre.UseCeiling;
                if (val > 0)
                {
                    newRst.Fxyhcount += 1;
                }
                else if (val < 0)
                {
                    newRst.Xcyhcount += 1;
                }
            }

            if (cur.IsCompletelyIsolated.HasValue && pre.IsCompletelyIsolated.HasValue)
            {
                var val = cur.IsCompletelyIsolated - pre.IsCompletelyIsolated;
                if (val > 0)
                {
                    newRst.Fxyhcount += 1;
                }
                else if (val < 0)
                {
                    newRst.Xcyhcount += 1;
                }
            }

            if (cur.UseFlammableMaterials.HasValue && pre.UseFlammableMaterials.HasValue)
            {
                var val = cur.UseFlammableMaterials - pre.UseFlammableMaterials;
                if (val > 0)
                {
                    newRst.Fxyhcount += 1;
                }
                else if (val < 0)
                {
                    newRst.Xcyhcount += 1;
                }
            }

            if (cur.UnauthorizedElectricalConnect.HasValue && pre.UnauthorizedElectricalConnect.HasValue)
            {
                var val = cur.UnauthorizedElectricalConnect - pre.UnauthorizedElectricalConnect;
                if (val > 0)
                {
                    newRst.Fxyhcount += 1;
                }
                else if (val < 0)
                {
                    newRst.Xcyhcount += 1;
                }
            }

            if (cur.ChargingFacilitiesNoStandard.HasValue && pre.ChargingFacilitiesNoStandard.HasValue)
            {
                var val = cur.ChargingFacilitiesNoStandard - pre.ChargingFacilitiesNoStandard;
                if (val > 0)
                {
                    newRst.Fxyhcount += 1;
                }
                else if (val < 0)
                {
                    newRst.Xcyhcount += 1;
                }
            }

            if (cur.NotEnoughExits.HasValue && pre.NotEnoughExits.HasValue)
            {
                var val = cur.NotEnoughExits - pre.NotEnoughExits;
                if (val > 0)
                {
                    newRst.Fxyhcount += 1;
                }
                else if (val < 0)
                {
                    newRst.Xcyhcount += 1;
                }
            }

            if (cur.UseFlammableInsulationMaterials.HasValue && pre.UseFlammableInsulationMaterials.HasValue)
            {
                var val = cur.UseFlammableInsulationMaterials - pre.UseFlammableInsulationMaterials;
                if (val > 0)
                {
                    newRst.Fxyhcount += 1;
                }
                else if (val < 0)
                {
                    newRst.Xcyhcount += 1;
                }
            }

            if (cur.UseFlammableDangerousGoods.HasValue && pre.UseFlammableDangerousGoods.HasValue)
            {
                var val = cur.UseFlammableDangerousGoods - pre.UseFlammableDangerousGoods;
                if (val > 0)
                {
                    newRst.Fxyhcount += 1;
                }
                else if (val < 0)
                {
                    newRst.Xcyhcount += 1;
                }
            }

            if (cur.AboveThreeFloorSelf.HasValue && pre.AboveThreeFloorSelf.HasValue)
            {
                var val = cur.AboveThreeFloorSelf - pre.AboveThreeFloorSelf;
                if (val < 0)
                {
                    newRst.Fxyhcount += 1;
                }
                else if (val > 0)
                {
                    newRst.Xcyhcount += 1;
                }
            }

            //if (cur.UseIndependentSmokeAlarm.HasValue && pre.UseIndependentSmokeAlarm.HasValue)
            //{
            //    var val = cur.UseIndependentSmokeAlarm - pre.UseIndependentSmokeAlarm;
            //    if (val < 0)
            //    {
            //        newRst.Fxyhcount += 1;
            //    }
            //    else if (val > 0)
            //    {
            //        newRst.Xcyhcount += 1;
            //    }
            //}

            //if (cur.AboveThreeFloorsEscape.HasValue && pre.AboveThreeFloorsEscape.HasValue)
            //{
            //    var val = cur.AboveThreeFloorsEscape - pre.AboveThreeFloorsEscape;
            //    if (val < 0)
            //    {
            //        newRst.Fxyhcount += 1;
            //    }
            //    else if (val > 0)
            //    {
            //        newRst.Xcyhcount += 1;
            //    }
            //}

            return newRst;
        }


        private Tuple<int, int> CalcYinHuanNum(ZmPlaceCommercialCheck request)
        {
            var numyhwtXf = 0;
            if (request.UseElectricBicycle.HasValue && request.UseElectricBicycle == 1)
            {
                numyhwtXf += 1;
            }

            if (request.UseBottledLiquefiedGas.HasValue && request.UseBottledLiquefiedGas == 1)
            {
                numyhwtXf += 1;
            }

            if (request.UseStackedItems.HasValue && request.UseStackedItems == 1)
            {
                numyhwtXf += 1;
            }

            if (request.UseBurglarMesh.HasValue && request.UseBurglarMesh == 1)
            {
                numyhwtXf += 1;
            }

            if (request.UseCeiling.HasValue && request.UseCeiling == 1)
            {
                numyhwtXf += 1;
            }

            if (request.IsCompletelyIsolated.HasValue && request.IsCompletelyIsolated == 1)
            {
                numyhwtXf += 1;
            }

            if (request.UseFlammableMaterials.HasValue && request.UseFlammableMaterials == 1)
            {
                numyhwtXf += 1;
            }

            if (request.UnauthorizedElectricalConnect.HasValue && request.UnauthorizedElectricalConnect == 1)
            {
                numyhwtXf += 1;
            }

            if (request.NotEnoughExits.HasValue && request.NotEnoughExits == 1)
            {
                numyhwtXf += 1;
            }

            if (request.FireServiceDamaged.HasValue && request.FireServiceDamaged == 1)
            {
                numyhwtXf += 1;
            }

            if (request.UseStoreDangerousGoods.HasValue && request.UseStoreDangerousGoods == 1)
            {
                numyhwtXf += 1;
            }

            if (request.ThreatenSafetyFireHazards.HasValue && request.ThreatenSafetyFireHazards == 1)
            {
                numyhwtXf += 1;
            }

            if (request.UseFlammableInsulationMaterials.HasValue && request.UseFlammableInsulationMaterials == 1)
            {
                numyhwtXf += 1;
            }

            if (request.UseFlammableDangerousGoods.HasValue && request.UseFlammableDangerousGoods == 1)
            {
                numyhwtXf += 1;
            }

            if (request.AboveThreeFloorSelf.HasValue && request.AboveThreeFloorSelf == 0)
            {
                numyhwtXf += 1;
            }

            var level = 2;
            if (numyhwtXf == 0)
            {
                level = 2;
            }
            else if (numyhwtXf >= 1 && numyhwtXf < 4)
            {
                level = 3;
            }
            else if (numyhwtXf >= 4)
            {
                level = 1;
            }

            return new Tuple<int, int>(numyhwtXf, level);
        }
        private Tuple<int, int> CalcYinHuanNum(ZmPlaceResident request)
        {
            var numyhwtXf = 0;
            if (request.UseElectricBicycle.HasValue && request.UseElectricBicycle == 1)
            {
                numyhwtXf += 1;
            }

            if (request.UseBottledLiquefiedGas.HasValue && request.UseBottledLiquefiedGas == 1)
            {
                numyhwtXf += 1;
            }

            if (request.UseStackedItems.HasValue && request.UseStackedItems == 1)
            {
                numyhwtXf += 1;
            }

            if (request.UseBurglarMesh.HasValue && request.UseBurglarMesh == 1)
            {
                numyhwtXf += 1;
            }

            if (request.UseCeiling.HasValue && request.UseCeiling == 1)
            {
                numyhwtXf += 1;
            }

            if (request.IsCompletelyIsolated.HasValue && request.IsCompletelyIsolated == 1)
            {
                numyhwtXf += 1;
            }

            if (request.UseFlammableMaterials.HasValue && request.UseFlammableMaterials == 1)
            {
                numyhwtXf += 1;
            }

            if (request.UnauthorizedElectricalConnect.HasValue && request.UnauthorizedElectricalConnect == 1)
            {
                numyhwtXf += 1;
            }

            if (request.ChargingFacilitiesNoStandard.HasValue && request.ChargingFacilitiesNoStandard == 1)
            {
                numyhwtXf += 1;
            }

            if (request.NotEnoughExits.HasValue && request.NotEnoughExits == 1)
            {
                numyhwtXf += 1;
            }

            if (request.UseFlammableInsulationMaterials.HasValue && request.UseFlammableInsulationMaterials == 1)
            {
                numyhwtXf += 1;
            }

            if (request.UseFlammableDangerousGoods.HasValue && request.UseFlammableDangerousGoods == 1)
            {
                numyhwtXf += 1;
            }

            if (request.AboveThreeFloorSelf.HasValue && request.AboveThreeFloorSelf == 0)
            {
                numyhwtXf += 1;
            }

            //if (request.UseIndependentSmokeAlarm.HasValue && request.UseIndependentSmokeAlarm == 0)
            //{
            //    numyhwtXf += 1;
            //}

            //if (request.AboveThreeFloorsEscape.HasValue && request.AboveThreeFloorsEscape == 0)
            //{
            //    numyhwtXf += 1;
            //}

            var level = 2;
            if (numyhwtXf == 0)
            {
                level = 2;
            }
            else if (numyhwtXf >= 1 && numyhwtXf < 4)
            {
                level = 3;
            }
            else if (numyhwtXf >= 4)
            {
                level = 1;
            }

            return new Tuple<int, int>(numyhwtXf, level);
        }
        private PcYhStatDto CalcPlaceCommercial(ZmPlaceCommercialCheck pre, ZmPlaceCommercialCheck cur)
        {
            var newRst = new PcYhStatDto();
            if (cur.UseElectricBicycle.HasValue && pre.UseElectricBicycle.HasValue)
            {
                var val = cur.UseElectricBicycle - pre.UseElectricBicycle;
                if (val > 0)
                {
                    newRst.Fxyhcount += 1;
                }
                else if (val < 0)
                {
                    newRst.Xcyhcount += 1;
                }
            }

            if (cur.UseBottledLiquefiedGas.HasValue && pre.UseBottledLiquefiedGas.HasValue)
            {
                var val = cur.UseBottledLiquefiedGas - pre.UseBottledLiquefiedGas;
                if (val > 0)
                {
                    newRst.Fxyhcount += 1;
                }
                else if (val < 0)
                {
                    newRst.Xcyhcount += 1;
                }
            }

            if (cur.UseStackedItems.HasValue && pre.UseStackedItems.HasValue)
            {
                var val = cur.UseStackedItems - pre.UseStackedItems;
                if (val > 0)
                {
                    newRst.Fxyhcount += 1;
                }
                else if (val < 0)
                {
                    newRst.Xcyhcount += 1;
                }
            }

            if (cur.UseBurglarMesh.HasValue && pre.UseBurglarMesh.HasValue)
            {
                var val = cur.UseBurglarMesh - pre.UseBurglarMesh;
                if (val > 0)
                {
                    newRst.Fxyhcount += 1;
                }
                else if (val < 0)
                {
                    newRst.Xcyhcount += 1;
                }
            }

            if (cur.UseCeiling.HasValue && pre.UseCeiling.HasValue)
            {
                var val = cur.UseCeiling - pre.UseCeiling;
                if (val > 0)
                {
                    newRst.Fxyhcount += 1;
                }
                else if (val < 0)
                {
                    newRst.Xcyhcount += 1;
                }
            }

            if (cur.IsCompletelyIsolated.HasValue && pre.IsCompletelyIsolated.HasValue)
            {
                var val = cur.IsCompletelyIsolated - pre.IsCompletelyIsolated;
                if (val > 0)
                {
                    newRst.Fxyhcount += 1;
                }
                else if (val < 0)
                {
                    newRst.Xcyhcount += 1;
                }
            }

            if (cur.UseFlammableMaterials.HasValue && pre.UseFlammableMaterials.HasValue)
            {
                var val = cur.UseFlammableMaterials - pre.UseFlammableMaterials;
                if (val > 0)
                {
                    newRst.Fxyhcount += 1;
                }
                else if (val < 0)
                {
                    newRst.Xcyhcount += 1;
                }
            }

            if (cur.UnauthorizedElectricalConnect.HasValue && pre.UnauthorizedElectricalConnect.HasValue)
            {
                var val = cur.UnauthorizedElectricalConnect - pre.UnauthorizedElectricalConnect;
                if (val > 0)
                {
                    newRst.Fxyhcount += 1;
                }
                else if (val < 0)
                {
                    newRst.Xcyhcount += 1;
                }
            }

            if (cur.NotEnoughExits.HasValue && pre.NotEnoughExits.HasValue)
            {
                var val = cur.NotEnoughExits - pre.NotEnoughExits;
                if (val > 0)
                {
                    newRst.Fxyhcount += 1;
                }
                else if (val < 0)
                {
                    newRst.Xcyhcount += 1;
                }
            }

            if (cur.FireServiceDamaged.HasValue && pre.FireServiceDamaged.HasValue)
            {
                var val = cur.FireServiceDamaged - pre.FireServiceDamaged;
                if (val > 0)
                {
                    newRst.Fxyhcount += 1;
                }
                else if (val < 0)
                {
                    newRst.Xcyhcount += 1;
                }
            }

            if (cur.UseStoreDangerousGoods.HasValue && pre.UseStoreDangerousGoods.HasValue)
            {
                var val = cur.UseStoreDangerousGoods - pre.UseStoreDangerousGoods;
                if (val > 0)
                {
                    newRst.Fxyhcount += 1;
                }
                else if (val < 0)
                {
                    newRst.Xcyhcount += 1;
                }
            }

            if (cur.ThreatenSafetyFireHazards.HasValue && pre.ThreatenSafetyFireHazards.HasValue)
            {
                var val = cur.ThreatenSafetyFireHazards - pre.ThreatenSafetyFireHazards;
                if (val > 0)
                {
                    newRst.Fxyhcount += 1;
                }
                else if (val < 0)
                {
                    newRst.Xcyhcount += 1;
                }
            }

            if (cur.UseFlammableInsulationMaterials.HasValue && pre.UseFlammableInsulationMaterials.HasValue)
            {
                var val = cur.UseFlammableInsulationMaterials - pre.UseFlammableInsulationMaterials;
                if (val > 0)
                {
                    newRst.Fxyhcount += 1;
                }
                else if (val < 0)
                {
                    newRst.Xcyhcount += 1;
                }
            }

            if (cur.UseFlammableDangerousGoods.HasValue && pre.UseFlammableDangerousGoods.HasValue)
            {
                var val = cur.UseFlammableDangerousGoods - pre.UseFlammableDangerousGoods;
                if (val > 0)
                {
                    newRst.Fxyhcount += 1;
                }
                else if (val < 0)
                {
                    newRst.Xcyhcount += 1;
                }
            }

            if (cur.AboveThreeFloorSelf.HasValue && pre.AboveThreeFloorSelf.HasValue)
            {
                var val = cur.AboveThreeFloorSelf - pre.AboveThreeFloorSelf;
                if (val < 0)
                {
                    newRst.Fxyhcount += 1;
                }
                else if (val > 0)
                {
                    newRst.Xcyhcount += 1;
                }
            }

            return newRst;
        }
        private Tuple<int, int> CalcYinHuanNum(ZmPlaceCommercial request)
        {
            var numyhwtXf = 0;
            if (request.UseElectricBicycle.HasValue && request.UseElectricBicycle == 1)
            {
                numyhwtXf += 1;
            }

            if (request.UseBottledLiquefiedGas.HasValue && request.UseBottledLiquefiedGas == 1)
            {
                numyhwtXf += 1;
            }

            if (request.UseStackedItems.HasValue && request.UseStackedItems == 1)
            {
                numyhwtXf += 1;
            }

            if (request.UseBurglarMesh.HasValue && request.UseBurglarMesh == 1)
            {
                numyhwtXf += 1;
            }

            if (request.UseCeiling.HasValue && request.UseCeiling == 1)
            {
                numyhwtXf += 1;
            }

            if (request.IsCompletelyIsolated.HasValue && request.IsCompletelyIsolated == 1)
            {
                numyhwtXf += 1;
            }

            if (request.UseFlammableMaterials.HasValue && request.UseFlammableMaterials == 1)
            {
                numyhwtXf += 1;
            }

            if (request.UnauthorizedElectricalConnect.HasValue && request.UnauthorizedElectricalConnect == 1)
            {
                numyhwtXf += 1;
            }

            if (request.NotEnoughExits.HasValue && request.NotEnoughExits == 1)
            {
                numyhwtXf += 1;
            }

            if (request.FireServiceDamaged.HasValue && request.FireServiceDamaged == 1)
            {
                numyhwtXf += 1;
            }

            if (request.UseStoreDangerousGoods.HasValue && request.UseStoreDangerousGoods == 1)
            {
                numyhwtXf += 1;
            }

            if (request.ThreatenSafetyFireHazards.HasValue && request.ThreatenSafetyFireHazards == 1)
            {
                numyhwtXf += 1;
            }

            if (request.UseFlammableInsulationMaterials.HasValue && request.UseFlammableInsulationMaterials == 1)
            {
                numyhwtXf += 1;
            }

            if (request.UseFlammableDangerousGoods.HasValue && request.UseFlammableDangerousGoods == 1)
            {
                numyhwtXf += 1;
            }

            if (request.AboveThreeFloorSelf.HasValue && request.AboveThreeFloorSelf == 0)
            {
                numyhwtXf += 1;
            }

            var level = 2;
            if (numyhwtXf == 0)
            {
                level = 2;
            }
            else if (numyhwtXf >= 1 && numyhwtXf < 4)
            {
                level = 3;
            }
            else if (numyhwtXf >= 4)
            {
                level = 1;
            }

            return new Tuple<int, int>(numyhwtXf, level);
        }


        [AllowAnonymous]
        [HttpGet("Hangfire/StatHiddenDangerInfo777")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<List<PcYhStatDto>>), 200)]

        public RespRet<List<PcYhStatDto>> StatHiddenDangerInfo777([FromQuery] string dfg, [FromQuery] bool reCount = false)
        {
            reCount = true;
            var Isloading = _cache.Get<bool>("Isloading");
            if (reCount == true)
            {
                if (Isloading == true && dfg == "1")
                {
                    return new RespRet<List<PcYhStatDto>>(false, "正在统计");
                }
            }

            var userNumber = "1380195087291650048";
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<List<PcYhStatDto>>(false, "user not logined");
            }

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<List<PcYhStatDto>>(false, "用户名不存在");
            }

            var curuserDto = _mapper.Map<UserDto>(user);
            if (curuserDto.UserGroup != UserGroupType.FireControl
                && curuserDto.Role != PermissionRoleType.Admin
                && curuserDto.Role != PermissionRoleType.City
                && curuserDto.Role != PermissionRoleType.Street)
            {
                return new RespRet<List<PcYhStatDto>>(false, "Not stat access");
            }

            var cacheData = _cache.Get<List<PcYhStatDto>>("PcYhStatData777");
            DateTime xf_fgcount = Convert.ToDateTime("2023-03-15");
            if (dfg == "1")
            {
                string sql = @"SELECT
cz.region_code AS villageCode,
jz.region_code AS TownCode,
qx.region_code Code,
cz.region_name AS village,
jz.region_name AS town,
qx.region_name as name,
COALESCE(to_char(createdate, 'YYYY-MM-DD HH24:MI:SS'), (SELECT to_char(createdate, 'YYYY-MM-DD HH24:MI:SS') FROM zm_xf_xy_count316 where check777=1 LIMIT 1)) AS createdate,
COALESCE(fxyhcount, '0') AS fxyhcount,
COALESCE(xcyhcount, '0') AS xcyhcount,
COALESCE(syyhcount, '0') AS syyhcount,
COALESCE(ypcfwcount, '0') AS ypcfwcount,
COALESCE(ypcfwcount_yh, '0') AS ypcfwcount_yh
FROM
zm_department cz
LEFT JOIN zm_department jz
ON jz.region_code = cz.parent_id
LEFT JOIN zm_department qx
ON jz.parent_id = qx.region_code
LEFT JOIN zm_xf_xy_count316
ON cz.region_code = zm_xf_xy_count316.villagecode
AND zm_xf_xy_count316.check777 = 1
WHERE
cz.check777 = 1
AND cz.regional_level = '7'
ORDER BY
cz.region_code";
                cacheData = _dbContext.Database.GetDbConnection().Query<PcYhStatDto>(sql).ToList();
                // cacheData = _cache.Get<List<PcYhStatDto>>("PcYhStatData777");
            }
            else if (dfg == "0")
            {
                //cacheData = _cache.Get<List<PcYhStatDto>>("PcYhStatData776");
                string sql = @"SELECT
cz.region_code AS villageCode,
jz.region_code AS TownCode,
qx.region_code Code,
cz.region_name AS village,
jz.region_name AS town,
qx.region_name as name,
COALESCE(to_char(createdate, 'YYYY-MM-DD HH24:MI:SS'), (SELECT to_char(createdate, 'YYYY-MM-DD HH24:MI:SS') FROM zm_xf_xy_count314 where check777=1 LIMIT 1)) AS createdate,
COALESCE(fxyhcount, '0') AS fxyhcount,
COALESCE(xcyhcount, '0') AS xcyhcount,
COALESCE(syyhcount, '0') AS syyhcount,
COALESCE(ypcfwcount, '0') AS ypcfwcount,
COALESCE(ypcfwcount_yh, '0') AS ypcfwcount_yh
FROM
zm_department cz
LEFT JOIN zm_department jz
ON jz.region_code = cz.parent_id
LEFT JOIN zm_department qx
ON jz.parent_id = qx.region_code
LEFT JOIN zm_xf_xy_count314
ON cz.region_code = zm_xf_xy_count314.villagecode

WHERE
cz.check777 = 1
AND cz.regional_level = '7'
ORDER BY
cz.region_code";
                cacheData = _dbContext.Database.GetDbConnection().Query<PcYhStatDto>(sql).ToList();
            }


            if (cacheData != null && !reCount)
            {//走缓存
                var result = new List<PcYhStatDto>();
                //市级管理员只看到区数据
                if (user.RoleType == 2 && user.Group == 2)
                {
                    JObject obj = JObject.Parse(user.District);
                    string RegionCode = (string)obj["RegionCode"]; // "John"
                    result = cacheData.GroupBy(o => o.Code)
                   .Select(g => new PcYhStatDto
                   {
                       Code = g.Key,
                       Syyhcount = g.Sum(o => o.Syyhcount),
                       Syyhcount_pc = g.Sum(o => o.Syyhcount_pc),
                       Name = g.Max(o => o.Name),
                       Xcyhcount = g.Sum(o => o.Xcyhcount),
                       Ypccount_ch = g.Sum(o => o.Ypccount_ch),
                       Ypccount_ch_yh = g.Sum(o => o.Ypccount_ch_yh),
                       Ypcfwcount = g.Sum(o => o.Ypcfwcount),
                       Ypcfwcount_yh = g.Sum(o => o.Ypcfwcount_yh),
                       Fxyhcount = g.Sum(o => o.Fxyhcount),
                       createdate = g.Max(o => o.createdate)
                   }).ToList();
                    result = result.OrderBy(c => c.Code).ToList();
                    return new RespRet<List<PcYhStatDto>>(true, "Ok", result);
                }
                else if (user.RoleType == 3 && user.Group == 2)
                {
                    JObject obj = JObject.Parse(user.District);
                    string RegionCode = (string)obj["RegionCode"]; // "John"
                    cacheData = cacheData.Where(c => c.Code == RegionCode).ToList();
                    result = cacheData.GroupBy(o => o.TownCode)
                  .Select(g => new PcYhStatDto
                  {
                      Code = g.Key,
                      Syyhcount = g.Sum(o => o.Syyhcount),
                      Syyhcount_pc = g.Sum(o => o.Syyhcount_pc),
                      Name = g.Max(o => o.Name),
                      Xcyhcount = g.Sum(o => o.Xcyhcount),
                      Ypccount_ch = g.Sum(o => o.Ypccount_ch),
                      Ypccount_ch_yh = g.Sum(o => o.Ypccount_ch_yh),
                      Ypcfwcount = g.Sum(o => o.Ypcfwcount),
                      Ypcfwcount_yh = g.Sum(o => o.Ypcfwcount_yh),
                      Fxyhcount = g.Sum(o => o.Fxyhcount),
                      createdate = g.Max(o => o.createdate),
                      Town = g.Max(o => o.Town),
                      TownCode = g.Max(o => o.TownCode)
                  }).ToList();
                    result = result.OrderBy(c => c.Code).ToList();
                    return new RespRet<List<PcYhStatDto>>(true, "Ok", result);
                }

                else if (user.RoleType == 4 && user.Group == 2)
                {
                    JObject obj = JObject.Parse(user.District);
                    string RegionCode = (string)obj["RegionCode"]; // "John"
                    cacheData = cacheData.Where(c => c.TownCode == RegionCode).ToList();
                    result = cacheData.GroupBy(o => o.villageCode)
                  .Select(g => new PcYhStatDto
                  {
                      Code = g.Key,
                      Syyhcount = g.Sum(o => o.Syyhcount),
                      Syyhcount_pc = g.Sum(o => o.Syyhcount_pc),
                      Name = g.Max(o => o.Name),
                      Xcyhcount = g.Sum(o => o.Xcyhcount),
                      Ypccount_ch = g.Sum(o => o.Ypccount_ch),
                      Ypccount_ch_yh = g.Sum(o => o.Ypccount_ch_yh),
                      Ypcfwcount = g.Sum(o => o.Ypcfwcount),
                      Ypcfwcount_yh = g.Sum(o => o.Ypcfwcount_yh),
                      Fxyhcount = g.Sum(o => o.Fxyhcount),
                      createdate = g.Max(o => o.createdate),
                      Town = g.Max(o => o.Town),
                      village = g.Max(o => o.village),
                      villageCode = g.Max(o => o.villageCode),
                      TownCode = g.Max(o => o.TownCode)
                  }).ToList();
                    result = result.OrderBy(c => c.Code).ToList();
                    return new RespRet<List<PcYhStatDto>>(true, "Ok", result);
                }
                cacheData = result.OrderBy(c => c.Code).ToList();
                return new RespRet<List<PcYhStatDto>>(true, "Ok", cacheData);
            }
            else
            {
                var datas = new List<PcYhStatDto>();
                //缓存
                if (dfg == "1")
                {
                    _cache.Set("Isloading107", true);
                    #region 计算777
                    //重新计算
                    List<ZmBuildingsLayer> houses = new List<ZmBuildingsLayer>();
                    var sbSql = new StringBuilder();
                    var sqlParams = new List<NpgsqlParameter>();
                    //消防数据时间分割数据池统计
                    if (dfg == "1")
                    {
                        sbSql.Append($@"select * from zm_buildings_layer where id in (SELECT id FROM zm_buildings where check777=1) and  (modified_on  >= '" + xf_fgcount + "') and is_delete is null");
                        //houses = _buildingsLayerRepository.Query().Where(_ => _.IsDelete != 1 && (_.CreateOn >= xf_fgcount || _.ModifiedOn >= xf_fgcount)).ToList();
                    }
                    else if (dfg == "0")
                    {
                        sbSql.Append($@"select * from zm_buildings_layer where id in (SELECT id FROM zm_buildings where check777=1) and  (modified_on <'" + xf_fgcount + "') and is_delete is null");
                        //houses = _buildingsLayerRepository.Query().Where(_ => _.IsDelete != 1 && (_.CreateOn >= xf_fgcount || _.ModifiedOn >= xf_fgcount)).ToList();
                    }
                    else
                    {
                        //houses = _buildingsLayerRepository.Query().Where(_ => (_.IsDelete != 1) && _.Village == "110108027206").ToList();
                        sbSql.Append($@"select * from zm_buildings_layer where id in (SELECT id FROM zm_buildings where check777=1)  and is_delete is null");
                    }
                    sbSql.Append($@" and 1 = @id");
                    sqlParams.Add(new NpgsqlParameter("id", 1));
                    houses = _dbContext.ZmBuildingsLayer.FromSqlRaw(sbSql.ToString(), sqlParams.ToArray()).ToList();
                    // var houses = _buildingsLayerRepository.Query().Where(_ => (_.Id == "0000b90ec62b4419bf0c7c769bbf35b0")).ToList();
                    if (houses == null || houses.Count < 1)
                    {
                        return new RespRet<List<PcYhStatDto>>(false, "no house data");
                    }
                    var dictHouse = new Dictionary<string, List<string>>();
                    var dictHouse_count = new Dictionary<string, int>();
                    var dictHouse_count_yh = new Dictionary<string, int>();
                    var dictHouse_count_yh_cs = new Dictionary<string, int>();
                    foreach (var item in houses)
                    {
                        if (dictHouse.ContainsKey(item.Village))
                        {
                            dictHouse[item.Village].Add(item.Id);
                        }
                        else
                        {
                            var list = new List<string>();
                            list.Add(item.Id);
                            dictHouse.Add(item.Village, list);
                            //消隐已排查房屋
                            int getcount = houses.Where(_ => (_.PcztFire == 20 || _.PcztFire == 21) && _.Village == item.Village).Count();
                            dictHouse_count.Add(item.Village, getcount);
                            //消隐已排查房屋有隐患
                            getcount = houses.Where(_ => (_.PcztFire == 21) && _.Village == item.Village).Count();
                            dictHouse_count_yh.Add(item.Village, getcount);
                        }
                    }

                    var residentDict = new Dictionary<string, PcYhStatDto>();
                    var commercialDict = new Dictionary<string, PcYhStatDto>();

                    int cs_count = 0;
                    int cs_count_yh = 0;
                    int Xcyhcount = 0;//排查隐患



                    #region 居住

                    List<ZmPlaceResident> placeResidentDatas = new List<ZmPlaceResident>();
                    List<ZmPlaceResidentCheck> placeResidentCheckDatas = new List<ZmPlaceResidentCheck>();

                    //消防数据时间分割数据池统计
                    if (dfg == "1")
                    {
                        placeResidentDatas = _placeResidentRepository
                        .Query(_ => _.Type == 0 && _.IsDelete != 1
                        && _.Pczt != null && _.Pczt > 1 && (_.ModifiedOn >= xf_fgcount))?.ToList();

                        placeResidentCheckDatas = _placeResidentCheckRepository.Query(_ => _.Type == 0)?.ToList();//核查数据
                    }
                    else if (dfg == "0")
                    {
                        placeResidentDatas = _placeResidentRepository
                        .Query(_ => _.Type == 0 && _.IsDelete != 1
                        && _.Pczt != null && _.Pczt > 1 && (_.ModifiedOn < xf_fgcount))?.ToList(); // 已排查的居住消防场所数据

                        placeResidentCheckDatas = _placeResidentCheckRepository.Query(_ => _.Type == 0)?.ToList();//核查数据
                    }
                    else
                    {
                        placeResidentDatas = _placeResidentRepository
                            .Query(_ => _.Type == 0 && _.IsDelete != 1
                            && _.Pczt != null && _.Pczt > 1)?.ToList(); // 已排查的居住消防场所数据

                        placeResidentCheckDatas = _placeResidentCheckRepository.Query(_ => _.Type == 0)?.ToList();//核查数据
                    }


                    if (placeResidentDatas != null && placeResidentDatas.Count > 0)
                    {
                        var pHIds = placeResidentDatas.Select(_ => _.HouseId).Distinct().ToList();
                        if (placeResidentCheckDatas != null && placeResidentCheckDatas.Count > 0) // 存在核查数据
                        {
                            foreach (var area in dictHouse)
                            {
                                var value = new PcYhStatDto { Code = area.Key };
                                residentDict.Add(area.Key, value);
                                var houseIds = area.Value.Where(_ => pHIds.Contains(_));

                                foreach (var hid in houseIds)
                                {
                                    var findPlaceResidentDatas = placeResidentDatas.FindAll(_ => _.HouseId == hid)?.ToList();
                                    ////排查隐患数量
                                    //int NumyhwtXfcount = (int)findPlaceResidentDatas.Sum(o => o.NumyhwtXf);
                                    //Syyhcount_pc
                                    //dictHouse_count_yh_cs[hid] = NumyhwtXfcount;

                                    foreach (var place in findPlaceResidentDatas)
                                    {
                                        //dictHouse_count_yh_cs.Add(place.Id, (int)place.NumyhwtXf);
                                        cs_count++;
                                        if (place.Pczt == 21)
                                        {
                                            cs_count_yh++;
                                        }
                                        var checkPlaces = placeResidentCheckDatas
                                            .FindAll(_ => _.PlaceId == place.Id)?.OrderBy(_ => _.CreateOn)?.ToList();
                                        if (checkPlaces != null && checkPlaces.Count > 0) // 存在此场所的核查数据
                                        {
                                            var pcData = checkPlaces.Find(_ => string.IsNullOrEmpty(_.CheckMind));
                                            if (pcData != null)
                                            {
                                                var aa = CalcYinHuanNum(pcData);
                                                residentDict[area.Key].Fxyhcount += aa.Item1;

                                                var findCheckDatas = checkPlaces.Where(_ => _.Id != pcData.Id)
                                                    .OrderBy(_ => _.CreateOn).ToList();
                                                for (int i = 0; i < findCheckDatas.Count; i++)
                                                {
                                                    if (i == 0)
                                                    {
                                                        var rst = CalcPlaceResident(pcData, findCheckDatas[i]);
                                                        residentDict[area.Key].Fxyhcount += rst.Fxyhcount;
                                                        residentDict[area.Key].Xcyhcount += rst.Xcyhcount;
                                                    }
                                                    else
                                                    {
                                                        var rst = CalcPlaceResident(findCheckDatas[i - 1], findCheckDatas[i]);
                                                        residentDict[area.Key].Fxyhcount += rst.Fxyhcount;
                                                        residentDict[area.Key].Xcyhcount += rst.Xcyhcount;
                                                    }
                                                }
                                            }
                                        }
                                        else
                                        {
                                            var aa = CalcYinHuanNum(place);
                                            residentDict[area.Key].Fxyhcount += aa.Item1;
                                            //commercialDict[area.Key].Fxyhcount += (int)place.NumyhwtXf;
                                        }
                                    }
                                }
                            }
                        }
                        else// 不存在核查数据
                        {
                            foreach (var area in dictHouse)
                            {
                                var value = new PcYhStatDto { Code = area.Key };
                                residentDict.Add(area.Key, value);
                                var houseIds = area.Value.Where(_ => pHIds.Contains(_));

                                foreach (var hid in houseIds)
                                {
                                    var findPlaceResidentDatas = placeResidentDatas.FindAll(_ => _.HouseId == hid)?.ToList();
                                    foreach (var place in findPlaceResidentDatas)
                                    {
                                        dictHouse_count_yh_cs.Add(place.Id, (int)place.NumyhwtXf);
                                        cs_count++;
                                        if (place.Pczt == 21)
                                        {
                                            cs_count_yh++;
                                        }
                                        var aa = CalcYinHuanNum(place);
                                        residentDict[area.Key].Fxyhcount += aa.Item1;
                                        //commercialDict[area.Key].Fxyhcount += (int)place.NumyhwtXf;
                                    }
                                }
                            }
                        }
                    }
                    #endregion




                    #region 商业




                    List<ZmPlaceCommercial> placeCommercialDatas = new List<ZmPlaceCommercial>();
                    List<ZmPlaceCommercialCheck> placeCommercialCheckDatas = new List<ZmPlaceCommercialCheck>();


                    //消防数据时间分割数据池统计
                    if (dfg == "1")
                    {
                        placeCommercialDatas = _placeCommercialRepository
                        .Query(_ => _.Type == 0 && _.IsDelete != 1
                        && _.Pczt != null && _.Pczt > 1 && (_.ModifiedOn >= xf_fgcount))?.ToList();
                        placeCommercialCheckDatas = _placeCommercialCheckRepository.Query(_ => _.Type == 0)?.ToList();//核查数据
                    }
                    else if (dfg == "0")
                    {
                        placeCommercialDatas = _placeCommercialRepository
                        .Query(_ => _.Type == 0 && _.IsDelete != 1
                        && _.Pczt != null && _.Pczt > 1 && (_.ModifiedOn < xf_fgcount))?.ToList(); // 已排查的居住消防场所数据
                        placeCommercialCheckDatas = _placeCommercialCheckRepository.Query(_ => _.Type == 0)?.ToList();//核查数据
                    }
                    else
                    {
                        placeCommercialDatas = _placeCommercialRepository
                            .Query(_ => _.Type == 0 && _.IsDelete != 1
                            && _.Pczt != null && _.Pczt > 1)?.ToList(); // 已排查的商业消防场所数据

                        placeCommercialCheckDatas = _placeCommercialCheckRepository
                        .Query(_ => _.Type == 0)?.ToList();//核查数据

                    }

                    if (placeCommercialDatas != null && placeCommercialDatas.Count > 0)
                    {
                        var pHIds = placeCommercialDatas.Select(_ => _.HouseId).Distinct().ToList();

                        if (placeCommercialCheckDatas != null && placeCommercialCheckDatas.Count > 0) // 存在核查数据
                        {
                            foreach (var area in dictHouse)
                            {
                                var value = new PcYhStatDto { Code = area.Key };
                                commercialDict.Add(area.Key, value);
                                var houseIds = area.Value.Where(_ => pHIds.Contains(_));

                                foreach (var hid in houseIds)
                                {
                                    var findPlaceDatas = placeCommercialDatas.FindAll(_ => _.HouseId == hid)?.ToList();
                                    foreach (var place in findPlaceDatas)
                                    {
                                        cs_count++;
                                        if (place.Pczt == 21)
                                        {
                                            cs_count_yh++;
                                        }
                                        Xcyhcount += (int)place.NumyhwtXf;
                                        var checkPlaces = placeCommercialCheckDatas
                                            .FindAll(_ => _.PlaceId == place.Id)?.OrderBy(_ => _.CreateOn)?.ToList();
                                        if (checkPlaces != null && checkPlaces.Count > 0) // 存在此场所的核查数据
                                        {
                                            var pcData = checkPlaces.Find(_ => string.IsNullOrEmpty(_.CheckMind));
                                            if (pcData != null)
                                            {
                                                var aa = CalcYinHuanNum(pcData);
                                                commercialDict[area.Key].Fxyhcount += aa.Item1;

                                                var findCheckDatas = checkPlaces.Where(_ => _.Id != pcData.Id)
                                                    .OrderBy(_ => _.CreateOn).ToList();
                                                for (int i = 0; i < findCheckDatas.Count; i++)
                                                {
                                                    if (i == 0)
                                                    {
                                                        var rst = CalcPlaceCommercial(pcData, findCheckDatas[i]);
                                                        commercialDict[area.Key].Fxyhcount += rst.Fxyhcount;
                                                        commercialDict[area.Key].Xcyhcount += rst.Xcyhcount;
                                                    }
                                                    else
                                                    {
                                                        var rst = CalcPlaceCommercial(findCheckDatas[i - 1], findCheckDatas[i]);
                                                        commercialDict[area.Key].Fxyhcount += rst.Fxyhcount;
                                                        commercialDict[area.Key].Xcyhcount += rst.Xcyhcount;
                                                    }
                                                }
                                            }
                                        }
                                        else
                                        {
                                            //var aa = CalcYinHuanNum(place);
                                            //commercialDict[area.Key].Fxyhcount += aa.Item1;
                                            //commercialDict[area.Key].Fxyhcount += (int)place.NumyhwtXf;
                                        }
                                    }
                                }
                            }
                        }
                        else// 不存在核查数据
                        {
                            foreach (var area in dictHouse)
                            {
                                var value = new PcYhStatDto { Code = area.Key };
                                commercialDict.Add(area.Key, value);
                                var houseIds = area.Value.Where(_ => pHIds.Contains(_));

                                foreach (var hid in houseIds)
                                {
                                    var findPlaceDatas = placeCommercialDatas.FindAll(_ => _.HouseId == hid)?.ToList();
                                    foreach (var place in findPlaceDatas)
                                    {
                                        cs_count++;
                                        if (place.Pczt == 21)
                                        {
                                            cs_count_yh++;
                                        }
                                        Xcyhcount += (int)place.NumyhwtXf;
                                        //var aa = CalcYinHuanNum(place);
                                        //commercialDict[area.Key].Fxyhcount += aa.Item1;
                                        //commercialDict[area.Key].Fxyhcount += (int)place.NumyhwtXf;
                                    }
                                }
                            }
                        }
                    }
                    #endregion
                    var totalDict = new Dictionary<string, PcYhStatDto>();
                    foreach (var item in residentDict)
                    {

                        if (totalDict.ContainsKey(item.Key))
                        {
                            totalDict[item.Key].Fxyhcount += item.Value.Fxyhcount;
                            totalDict[item.Key].Xcyhcount += item.Value.Xcyhcount;

                        }
                        else
                        {
                            var value = new PcYhStatDto { Code = item.Key };
                            value.Fxyhcount = item.Value.Fxyhcount;
                            value.Xcyhcount = item.Value.Xcyhcount;

                            //已排查-房屋
                            var getYpcfwcount = dictHouse_count.Where(q => q.Key == item.Key).FirstOrDefault();
                            value.Ypcfwcount = getYpcfwcount.Value;
                            //消隐-房屋
                            getYpcfwcount = dictHouse_count_yh.Where(q => q.Key == item.Key).FirstOrDefault();
                            value.Ypcfwcount_yh = getYpcfwcount.Value;

                            string sqlxfyh = "select  COALESCE((select sum(aa.numyhwt_xf) from zm_place_commercial  aa,zm_buildings_layer  bb  where  aa.id in (SELECT id FROM zm_buildings where check777=1) and  aa.house_id=bb.ID and bb.village='" + item.Key + "'and  aa.is_delete IS NULL AND aa.type = 0 AND aa.pczt > 1 ),0)+ COALESCE((select sum(aa.numyhwt_xf) from zm_place_resident  aa,zm_buildings_layer  bb  where  aa.house_id=bb.ID and bb.village='" + item.Key + "'and  aa.is_delete IS NULL AND aa.type = 0 AND aa.pczt > 1),0) dd ";

                            if (dfg == "1")
                            {
                                sqlxfyh = "select  COALESCE((select sum(aa.numyhwt_xf) from zm_place_commercial  aa,zm_buildings_layer  bb  where aa.id in (SELECT id FROM zm_buildings where check777=1) and  aa.house_id=bb.ID and bb.village='" + item.Key + "'and  aa.is_delete IS NULL AND aa.type = 0 AND aa.pczt > 1 and (aa.modified_on >='" + xf_fgcount + "') ),0)+ COALESCE((select sum(aa.numyhwt_xf) from zm_place_resident  aa,zm_buildings_layer  bb  where  aa.house_id=bb.ID and bb.village='" + item.Key + "'and  aa.is_delete IS NULL AND aa.type = 0 AND aa.pczt > 1 and ( aa.modified_on >='" + xf_fgcount + "')),0) dd ";

                            }
                            else if (dfg == "0")
                            {
                                sqlxfyh = "select  COALESCE((select sum(aa.numyhwt_xf) from zm_place_commercial  aa,zm_buildings_layer  bb  where aa.id in (SELECT id FROM zm_buildings where check777=1) and  aa.house_id=bb.ID and bb.village='" + item.Key + "'and  aa.is_delete IS NULL AND aa.type = 0 AND aa.pczt > 1 and ( aa.modified_on<'" + xf_fgcount + "') ),0)+ COALESCE((select sum(aa.numyhwt_xf) from zm_place_resident  aa,zm_buildings_layer  bb  where  aa.house_id=bb.ID and bb.village='" + item.Key + "'and  aa.is_delete IS NULL AND aa.type = 0 AND aa.pczt > 1 and ( aa.modified_on<'" + xf_fgcount + "')),0) dd ";
                            }
                            else
                            {
                                sqlxfyh = "select  COALESCE((select sum(aa.numyhwt_xf) from zm_place_commercial  aa,zm_buildings_layer  bb  where aa.id in (SELECT id FROM zm_buildings where check777=1) and  aa.house_id=bb.ID and bb.village='" + item.Key + "'and  aa.is_delete IS NULL AND aa.type = 0 AND aa.pczt > 1 ),0)+ COALESCE((select sum(aa.numyhwt_xf) from zm_place_resident  aa,zm_buildings_layer  bb  where  aa.house_id=bb.ID and bb.village='" + item.Key + "'and  aa.is_delete IS NULL AND aa.type = 0 AND aa.pczt > 1),0) dd ";
                            }

                            var xfyh_count = _dbContext.Database.GetDbConnection().Query<long>(sqlxfyh).FirstOrDefault();
                            value.Syyhcount_pc = xfyh_count;


                            totalDict.Add(item.Key, value);
                        }
                    }
                    foreach (var item in commercialDict)
                    {
                        if (totalDict.ContainsKey(item.Key))
                        {
                            totalDict[item.Key].Fxyhcount += item.Value.Fxyhcount;
                            totalDict[item.Key].Xcyhcount += item.Value.Xcyhcount;
                        }
                        else
                        {
                            var value = new PcYhStatDto { Code = item.Key };
                            value.Fxyhcount = item.Value.Fxyhcount;
                            value.Xcyhcount = item.Value.Xcyhcount;
                            totalDict.Add(item.Key, value);
                        }
                    }


                    foreach (var item in totalDict)
                    {
                        item.Value.Syyhcount = item.Value.Fxyhcount - item.Value.Xcyhcount;
                        datas.Add(item.Value);
                    }

                    var departments = _cache.Get("departments") as List<ZmDepartment>;
                    if (null == departments)
                    {
                        departments = _departmentRepository.Query().ToList();
                        _cache.Set("departments", departments);
                    }

                    foreach (var item in datas)
                    {

                        var findpidvillage = departments.Find(_ => _.RegionCode == item.Code);


                        if (findpidvillage != null)
                        {
                            item.village = findpidvillage?.RegionName;
                            item.villageCode = findpidvillage?.RegionCode;

                            var find1Town = departments.Find(_ => _.RegionCode == findpidvillage.ParentId);
                            if (find1Town != null)
                            {
                                item.Town = find1Town?.RegionName;
                                item.TownCode = find1Town?.RegionCode;

                                var findpidxzq = departments.Find(_ => _.RegionCode == find1Town.ParentId);
                                if (findpidxzq != null)
                                {
                                    item.Name = findpidxzq?.RegionName;
                                    item.Code = findpidxzq?.RegionCode;
                                }
                            }

                        }


                    }
                    #endregion
                    _cache.Set("PcYhStatData777", datas);
                }
                else if (dfg == "0")
                {
                    string strsql = "SELECT xzq as name ,xzqcode as code,town,towncode, village, villagecode, fxyhcount, xcyhcount, syyhcount, ypcfwcount, ypcfwcount_yh FROM zm_xf_xy_count314 where villagecode in (select xzqdm from summary777) order by villagecode asc";
                    var datasold777 = _dbContext.Database.GetDbConnection().Query<PcYhStatDto>(strsql).ToList();
                    datas = datasold777;
                    _cache.Set("PcYhStatData776", datasold777);
                }
                if (dfg == "1")
                {
                    string del = "delete from zm_xf_xy_count316 where check777=1 ";
                    var dela = _dbContext.Database.ExecuteSqlRaw(del);
                    foreach (var v in datas)
                    {
                        var d = Guid.NewGuid().ToString();
                        string dd = DateTime.Now.ToString();
                        string sql = " INSERT INTO public.zm_xf_xy_count316 " +
                            "(id, xzq, xzqcode, town, towncode, village, villagecode, fxyhcount, xcyhcount, syyhcount, ypcfwcount, ypcfwcount_yh, createdate,check777) VALUES" +
                            " ('" + d + "', '" + v.Name + "', '" + v.Code + "', '" + v.Town + "', '" + v.TownCode + "', '" + v.village + "', '" + v.villageCode + "', '" + v.Fxyhcount + "', '" + v.Xcyhcount + "', '" + v.Syyhcount + "', '" + v.Ypcfwcount + "', '" + v.Ypcfwcount_yh + "',  '" + dd + "',1);";
                        var num = _dbContext.Database.ExecuteSqlRaw(sql);
                    }
                    _cache.Set("Isloading107", false);
                }
                if (dfg == "1")
                {
                    string sql = @"SELECT
cz.region_code AS villageCode,
jz.region_code AS TownCode,
qx.region_code Code,
cz.region_name AS village,
jz.region_name AS town,
qx.region_name as name,
COALESCE(to_char(createdate, 'YYYY-MM-DD HH24:MI:SS'), (SELECT to_char(createdate, 'YYYY-MM-DD HH24:MI:SS') FROM zm_xf_xy_count316 where check777=1 LIMIT 1)) AS createdate,
COALESCE(fxyhcount, '0') AS fxyhcount,
COALESCE(xcyhcount, '0') AS xcyhcount,
COALESCE(syyhcount, '0') AS syyhcount,
COALESCE(ypcfwcount, '0') AS ypcfwcount,
COALESCE(ypcfwcount_yh, '0') AS ypcfwcount_yh
FROM
zm_department cz
LEFT JOIN zm_department jz
ON jz.region_code = cz.parent_id
LEFT JOIN zm_department qx
ON jz.parent_id = qx.region_code
LEFT JOIN zm_xf_xy_count316
ON cz.region_code = zm_xf_xy_count316.villagecode
AND zm_xf_xy_count316.check777 = 1
WHERE
cz.check777 = 1
AND cz.regional_level = '7'
ORDER BY
cz.region_code";
                    datas = _dbContext.Database.GetDbConnection().Query<PcYhStatDto>(sql).ToList();
                    // cacheData = _cache.Get<List<PcYhStatDto>>("PcYhStatData777");
                }
                else if (dfg == "0")
                {
                    //cacheData = _cache.Get<List<PcYhStatDto>>("PcYhStatData776");
                    string sql = @"SELECT
cz.region_code AS villageCode,
jz.region_code AS TownCode,
qx.region_code Code,
cz.region_name AS village,
jz.region_name AS town,
qx.region_name as name,
COALESCE(to_char(createdate, 'YYYY-MM-DD HH24:MI:SS'), (SELECT to_char(createdate, 'YYYY-MM-DD HH24:MI:SS') FROM zm_xf_xy_count314 where check777=1 LIMIT 1)) AS createdate,
COALESCE(fxyhcount, '0') AS fxyhcount,
COALESCE(xcyhcount, '0') AS xcyhcount,
COALESCE(syyhcount, '0') AS syyhcount,
COALESCE(ypcfwcount, '0') AS ypcfwcount,
COALESCE(ypcfwcount_yh, '0') AS ypcfwcount_yh
FROM
zm_department cz
LEFT JOIN zm_department jz
ON jz.region_code = cz.parent_id
LEFT JOIN zm_department qx
ON jz.parent_id = qx.region_code
LEFT JOIN zm_xf_xy_count314
ON cz.region_code = zm_xf_xy_count314.villagecode

WHERE
cz.check777 = 1
AND cz.regional_level = '7'
ORDER BY
cz.region_code";
                    datas = _dbContext.Database.GetDbConnection().Query<PcYhStatDto>(sql).ToList();
                }

                //市级管理员只看到区数据
                if (user.RoleType == 2 && user.Group == 2)
                {
                    JObject obj = JObject.Parse(user.District);
                    string RegionCode = (string)obj["RegionCode"]; // "John"
                    datas = datas.GroupBy(o => o.Code)
                   .Select(g => new PcYhStatDto
                   {
                       Code = g.Key,
                       Syyhcount = g.Sum(o => o.Syyhcount),
                       Syyhcount_pc = g.Sum(o => o.Syyhcount_pc),
                       Name = g.Max(o => o.Name),
                       Xcyhcount = g.Sum(o => o.Xcyhcount),
                       Ypccount_ch = g.Sum(o => o.Ypccount_ch),
                       Ypccount_ch_yh = g.Sum(o => o.Ypccount_ch_yh),
                       Ypcfwcount = g.Sum(o => o.Ypcfwcount),
                       Ypcfwcount_yh = g.Sum(o => o.Ypcfwcount_yh),
                       Fxyhcount = g.Sum(o => o.Fxyhcount),
                       createdate = g.Max(o => o.createdate)
                   }).ToList();
                    datas = datas.OrderBy(c => c.Code).ToList();
                    return new RespRet<List<PcYhStatDto>>(true, "Ok", datas);
                }
                else if (user.RoleType == 3 && user.Group == 2)
                {
                    JObject obj = JObject.Parse(user.District);
                    string RegionCode = (string)obj["RegionCode"]; // "John"
                    datas = datas.Where(c => c.Code == RegionCode).ToList();
                    datas = datas.GroupBy(o => o.TownCode)
                  .Select(g => new PcYhStatDto
                  {
                      Code = g.Key,
                      Syyhcount = g.Sum(o => o.Syyhcount),
                      Syyhcount_pc = g.Sum(o => o.Syyhcount_pc),
                      Name = g.Max(o => o.Name),
                      Xcyhcount = g.Sum(o => o.Xcyhcount),
                      Ypccount_ch = g.Sum(o => o.Ypccount_ch),
                      Ypccount_ch_yh = g.Sum(o => o.Ypccount_ch_yh),
                      Ypcfwcount = g.Sum(o => o.Ypcfwcount),
                      Ypcfwcount_yh = g.Sum(o => o.Ypcfwcount_yh),
                      Fxyhcount = g.Sum(o => o.Fxyhcount),
                      createdate = g.Max(o => o.createdate),
                      Town = g.Max(o => o.Town),
                      TownCode = g.Max(o => o.TownCode)
                  }).ToList();
                }
                else if (user.RoleType == 4 && user.Group == 2)
                {
                    JObject obj = JObject.Parse(user.District);
                    string RegionCode = (string)obj["RegionCode"]; // "John"
                    datas = datas.Where(c => c.TownCode == RegionCode).ToList();
                    datas = datas.GroupBy(o => o.villageCode)
                  .Select(g => new PcYhStatDto
                  {
                      Code = g.Key,
                      Syyhcount = g.Sum(o => o.Syyhcount),
                      Syyhcount_pc = g.Sum(o => o.Syyhcount_pc),
                      Name = g.Max(o => o.Name),
                      Xcyhcount = g.Sum(o => o.Xcyhcount),
                      Ypccount_ch = g.Sum(o => o.Ypccount_ch),
                      Ypccount_ch_yh = g.Sum(o => o.Ypccount_ch_yh),
                      Ypcfwcount = g.Sum(o => o.Ypcfwcount),
                      Ypcfwcount_yh = g.Sum(o => o.Ypcfwcount_yh),
                      Fxyhcount = g.Sum(o => o.Fxyhcount),
                      createdate = g.Max(o => o.createdate),
                      Town = g.Max(o => o.Town),
                      village = g.Max(o => o.village),
                      villageCode = g.Max(o => o.villageCode),
                      TownCode = g.Max(o => o.TownCode)
                  }).ToList();
                    datas = datas.OrderBy(c => c.Code).ToList();
                    return new RespRet<List<PcYhStatDto>>(true, "Ok", datas);
                }
                datas = datas.OrderBy(c => c.Code).ToList();
                return new RespRet<List<PcYhStatDto>>(true, "Ok", datas);
            }


        }
        [AllowAnonymous]
        [HttpGet("Hangfire/StatHiddenDangerInfo107")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<List<PcYhStatDto>>), 200)]

        public RespRet<List<PcYhStatDto>> StatHiddenDangerInfo107([FromQuery] string dfg, [FromQuery] bool reCount = false)
        {
            reCount = true;
            var Isloading = _cache.Get<bool>("Isloading107");
            if (reCount == true)
            {
                if (Isloading == true && dfg == "1")
                {
                    return new RespRet<List<PcYhStatDto>>(false, "正在统计");
                }
            }
            var userNumber = "1380195087291650048";
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<List<PcYhStatDto>>(false, "user not logined");
            }

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<List<PcYhStatDto>>(false, "用户名不存在");
            }

            var curuserDto = _mapper.Map<UserDto>(user);
            if (curuserDto.UserGroup != UserGroupType.FireControl
                && curuserDto.Role != PermissionRoleType.Admin
                && curuserDto.Role != PermissionRoleType.City
                && curuserDto.Role != PermissionRoleType.Street)
            {
                return new RespRet<List<PcYhStatDto>>(false, "Not stat access");
            }

            var cacheData = _cache.Get<List<PcYhStatDto>>("PcYhStatData107");
            DateTime xf_fgcount = Convert.ToDateTime("2023-03-15");
            if (dfg == "1")
            {
                string sql = @"SELECT
cz.region_code AS villageCode,
jz.region_code AS TownCode,
qx.region_code Code,
cz.region_name AS village,
jz.region_name AS town,
qx.region_name as name,
COALESCE(to_char(createdate, 'YYYY-MM-DD HH24:MI:SS'), (SELECT to_char(createdate, 'YYYY-MM-DD HH24:MI:SS') FROM zm_xf_xy_count316 where check107=1 LIMIT 1)) AS createdate,
COALESCE(fxyhcount, '0') AS fxyhcount,
COALESCE(xcyhcount, '0') AS xcyhcount,
COALESCE(syyhcount, '0') AS syyhcount,
COALESCE(ypcfwcount, '0') AS ypcfwcount,
COALESCE(ypcfwcount_yh, '0') AS ypcfwcount_yh
FROM
zm_department cz
LEFT JOIN zm_department jz
ON jz.region_code = cz.parent_id
LEFT JOIN zm_department qx
ON jz.parent_id = qx.region_code
LEFT JOIN zm_xf_xy_count316
ON cz.region_code = zm_xf_xy_count316.villagecode
AND zm_xf_xy_count316.check107 = 1
WHERE
cz.check107 = 1
AND cz.regional_level = '7'
ORDER BY
cz.region_code";
                cacheData = _dbContext.Database.GetDbConnection().Query<PcYhStatDto>(sql).ToList();
                // cacheData = _cache.Get<List<PcYhStatDto>>("PcYhStatData777");
            }
            else if (dfg == "0")
            {
                string sql = @"SELECT
cz.region_code AS villageCode,
jz.region_code AS TownCode,
qx.region_code Code,
cz.region_name AS village,
jz.region_name AS town,
qx.region_name as name,
COALESCE(to_char(createdate, 'YYYY-MM-DD HH24:MI:SS'), (SELECT to_char(createdate, 'YYYY-MM-DD HH24:MI:SS') FROM zm_xf_xy_count314 where check107=1 LIMIT 1)) AS createdate,
COALESCE(fxyhcount, '0') AS fxyhcount,
COALESCE(xcyhcount, '0') AS xcyhcount,
COALESCE(syyhcount, '0') AS syyhcount,
COALESCE(ypcfwcount, '0') AS ypcfwcount,
COALESCE(ypcfwcount_yh, '0') AS ypcfwcount_yh
FROM
zm_department cz
LEFT JOIN zm_department jz
ON jz.region_code = cz.parent_id
LEFT JOIN zm_department qx
ON jz.parent_id = qx.region_code
LEFT JOIN zm_xf_xy_count314
ON cz.region_code = zm_xf_xy_count314.villagecode

WHERE
cz.check107 = 1
AND cz.regional_level = '7'
ORDER BY
cz.region_code";
                cacheData = _dbContext.Database.GetDbConnection().Query<PcYhStatDto>(sql).ToList();
            }


            if (cacheData != null && !reCount)
            {//走缓存

                var result = new List<PcYhStatDto>();
                //市级管理员只看到区数据
                if (user.RoleType == 2 && user.Group == 2)
                {
                    JObject obj = JObject.Parse(user.District);
                    string RegionCode = (string)obj["RegionCode"]; // "John"
                    result = cacheData.GroupBy(o => o.Code)
                   .Select(g => new PcYhStatDto
                   {
                       Code = g.Key,
                       Syyhcount = g.Sum(o => o.Syyhcount),
                       Syyhcount_pc = g.Sum(o => o.Syyhcount_pc),
                       Name = g.Max(o => o.Name),
                       Xcyhcount = g.Sum(o => o.Xcyhcount),
                       Ypccount_ch = g.Sum(o => o.Ypccount_ch),
                       Ypccount_ch_yh = g.Sum(o => o.Ypccount_ch_yh),
                       Ypcfwcount = g.Sum(o => o.Ypcfwcount),
                       Ypcfwcount_yh = g.Sum(o => o.Ypcfwcount_yh),
                       Fxyhcount = g.Sum(o => o.Fxyhcount),
                       createdate = g.Max(o => o.createdate)
                   }).ToList();
                    result = result.OrderBy(c => c.Code).ToList();
                    return new RespRet<List<PcYhStatDto>>(true, "Ok", result);
                }
                else if (user.RoleType == 3 && user.Group == 2)
                {
                    JObject obj = JObject.Parse(user.District);
                    string RegionCode = (string)obj["RegionCode"]; // "John"
                    cacheData = cacheData.Where(c => c.Code == RegionCode).ToList();
                    result = cacheData.GroupBy(o => o.TownCode)
                  .Select(g => new PcYhStatDto
                  {
                      Code = g.Key,
                      Syyhcount = g.Sum(o => o.Syyhcount),
                      Syyhcount_pc = g.Sum(o => o.Syyhcount_pc),
                      Name = g.Max(o => o.Name),
                      Xcyhcount = g.Sum(o => o.Xcyhcount),
                      Ypccount_ch = g.Sum(o => o.Ypccount_ch),
                      Ypccount_ch_yh = g.Sum(o => o.Ypccount_ch_yh),
                      Ypcfwcount = g.Sum(o => o.Ypcfwcount),
                      Ypcfwcount_yh = g.Sum(o => o.Ypcfwcount_yh),
                      Fxyhcount = g.Sum(o => o.Fxyhcount),
                      createdate = g.Max(o => o.createdate),
                      Town = g.Max(o => o.Town),
                      TownCode = g.Max(o => o.TownCode)
                  }).ToList();
                    result = result.OrderBy(c => c.Code).ToList();
                    return new RespRet<List<PcYhStatDto>>(true, "Ok", result);
                }

                else if (user.RoleType == 4 && user.Group == 2)
                {
                    JObject obj = JObject.Parse(user.District);
                    string RegionCode = (string)obj["RegionCode"]; // "John"
                    cacheData = cacheData.Where(c => c.TownCode == RegionCode).ToList();
                    result = cacheData.GroupBy(o => o.villageCode)
                  .Select(g => new PcYhStatDto
                  {
                      Code = g.Key,
                      Syyhcount = g.Sum(o => o.Syyhcount),
                      Syyhcount_pc = g.Sum(o => o.Syyhcount_pc),
                      Name = g.Max(o => o.Name),
                      Xcyhcount = g.Sum(o => o.Xcyhcount),
                      Ypccount_ch = g.Sum(o => o.Ypccount_ch),
                      Ypccount_ch_yh = g.Sum(o => o.Ypccount_ch_yh),
                      Ypcfwcount = g.Sum(o => o.Ypcfwcount),
                      Ypcfwcount_yh = g.Sum(o => o.Ypcfwcount_yh),
                      Fxyhcount = g.Sum(o => o.Fxyhcount),
                      createdate = g.Max(o => o.createdate),
                      Town = g.Max(o => o.Town),
                      village = g.Max(o => o.village),
                      villageCode = g.Max(o => o.villageCode),
                      TownCode = g.Max(o => o.TownCode)
                  }).ToList();
                    result = result.OrderBy(c => c.Code).ToList();
                    return new RespRet<List<PcYhStatDto>>(true, "Ok", result);
                }
                cacheData = result.OrderBy(c => c.Code).ToList();
                return new RespRet<List<PcYhStatDto>>(true, "Ok", cacheData);
            }
            else
            {


                var datas = new List<PcYhStatDto>();
                //缓存
                if (dfg == "1")
                {
                    _cache.Set("Isloading107", true);
                    #region 计算107
                    //重新计算
                    List<ZmBuildingsLayer> houses = new List<ZmBuildingsLayer>();
                    var sbSql = new StringBuilder();
                    var sqlParams = new List<NpgsqlParameter>();
                    //消防数据时间分割数据池统计
                    if (dfg == "1")
                    {
                        sbSql.Append($@"select * from zm_buildings_layer where id in (SELECT id FROM zm_buildings where check107=1) and  (create_on  >=  '" + xf_fgcount + "'  or modified_on  >= '" + xf_fgcount + "') and is_delete is null");
                        //houses = _buildingsLayerRepository.Query().Where(_ => _.IsDelete != 1 && (_.CreateOn >= xf_fgcount || _.ModifiedOn >= xf_fgcount)).ToList();
                    }
                    else if (dfg == "0")
                    {
                        sbSql.Append($@"select * from zm_buildings_layer where id in (SELECT id FROM zm_buildings where check107=1) and  (create_on  < '" + xf_fgcount + "'  or modified_on <'" + xf_fgcount + "') and is_delete is null");
                        //houses = _buildingsLayerRepository.Query().Where(_ => _.IsDelete != 1 && (_.CreateOn >= xf_fgcount || _.ModifiedOn >= xf_fgcount)).ToList();
                    }
                    else
                    {
                        //houses = _buildingsLayerRepository.Query().Where(_ => (_.IsDelete != 1) && _.Village == "110108027206").ToList();
                        sbSql.Append($@"select * from zm_buildings_layer where id in (SELECT id FROM zm_buildings where check107=1)  and is_delete is null");
                    }
                    sbSql.Append($@" and 1 = @id");
                    sqlParams.Add(new NpgsqlParameter("id", 1));
                    houses = _dbContext.ZmBuildingsLayer.FromSqlRaw(sbSql.ToString(), sqlParams.ToArray()).ToList();
                    // var houses = _buildingsLayerRepository.Query().Where(_ => (_.Id == "0000b90ec62b4419bf0c7c769bbf35b0")).ToList();
                    if (houses == null || houses.Count < 1)
                    {
                        return new RespRet<List<PcYhStatDto>>(false, "no house data");
                    }
                    var dictHouse = new Dictionary<string, List<string>>();
                    var dictHouse_count = new Dictionary<string, int>();
                    var dictHouse_count_yh = new Dictionary<string, int>();
                    var dictHouse_count_yh_cs = new Dictionary<string, int>();
                    foreach (var item in houses)
                    {
                        if (dictHouse.ContainsKey(item.Village))
                        {
                            dictHouse[item.Village].Add(item.Id);
                        }
                        else
                        {
                            var list = new List<string>();
                            list.Add(item.Id);
                            dictHouse.Add(item.Village, list);
                            //消隐已排查房屋
                            int getcount = houses.Where(_ => (_.PcztFire == 20 || _.PcztFire == 21) && _.Village == item.Village).Count();
                            dictHouse_count.Add(item.Village, getcount);
                            //消隐已排查房屋有隐患
                            getcount = houses.Where(_ => (_.PcztFire == 21) && _.Village == item.Village).Count();
                            dictHouse_count_yh.Add(item.Village, getcount);
                        }
                    }

                    var residentDict = new Dictionary<string, PcYhStatDto>();
                    var commercialDict = new Dictionary<string, PcYhStatDto>();

                    int cs_count = 0;
                    int cs_count_yh = 0;
                    int Xcyhcount = 0;//排查隐患



                    #region 居住

                    List<ZmPlaceResident> placeResidentDatas = new List<ZmPlaceResident>();
                    List<ZmPlaceResidentCheck> placeResidentCheckDatas = new List<ZmPlaceResidentCheck>();

                    //消防数据时间分割数据池统计
                    if (dfg == "1")
                    {
                        placeResidentDatas = _placeResidentRepository
                        .Query(_ => _.Type == 0 && _.IsDelete != 1
                        && _.Pczt != null && _.Pczt > 1 && (_.CreateOn >= xf_fgcount || _.ModifiedOn >= xf_fgcount))?.ToList();

                        placeResidentCheckDatas = _placeResidentCheckRepository.Query(_ => _.Type == 0)?.ToList();//核查数据
                    }
                    else if (dfg == "0")
                    {
                        placeResidentDatas = _placeResidentRepository
                        .Query(_ => _.Type == 0 && _.IsDelete != 1
                        && _.Pczt != null && _.Pczt > 1 && (_.CreateOn < xf_fgcount || _.ModifiedOn < xf_fgcount))?.ToList(); // 已排查的居住消防场所数据

                        placeResidentCheckDatas = _placeResidentCheckRepository.Query(_ => _.Type == 0)?.ToList();//核查数据
                    }
                    else
                    {
                        placeResidentDatas = _placeResidentRepository
                            .Query(_ => _.Type == 0 && _.IsDelete != 1
                            && _.Pczt != null && _.Pczt > 1)?.ToList(); // 已排查的居住消防场所数据

                        placeResidentCheckDatas = _placeResidentCheckRepository.Query(_ => _.Type == 0)?.ToList();//核查数据
                    }


                    if (placeResidentDatas != null && placeResidentDatas.Count > 0)
                    {
                        var pHIds = placeResidentDatas.Select(_ => _.HouseId).Distinct().ToList();
                        if (placeResidentCheckDatas != null && placeResidentCheckDatas.Count > 0) // 存在核查数据
                        {
                            foreach (var area in dictHouse)
                            {
                                var value = new PcYhStatDto { Code = area.Key };
                                residentDict.Add(area.Key, value);
                                var houseIds = area.Value.Where(_ => pHIds.Contains(_));

                                foreach (var hid in houseIds)
                                {
                                    var findPlaceResidentDatas = placeResidentDatas.FindAll(_ => _.HouseId == hid)?.ToList();
                                    ////排查隐患数量
                                    //int NumyhwtXfcount = (int)findPlaceResidentDatas.Sum(o => o.NumyhwtXf);
                                    //Syyhcount_pc
                                    //dictHouse_count_yh_cs[hid] = NumyhwtXfcount;

                                    foreach (var place in findPlaceResidentDatas)
                                    {
                                        //dictHouse_count_yh_cs.Add(place.Id, (int)place.NumyhwtXf);
                                        cs_count++;
                                        if (place.Pczt == 21)
                                        {
                                            cs_count_yh++;
                                        }
                                        var checkPlaces = placeResidentCheckDatas
                                            .FindAll(_ => _.PlaceId == place.Id)?.OrderBy(_ => _.CreateOn)?.ToList();
                                        if (checkPlaces != null && checkPlaces.Count > 0) // 存在此场所的核查数据
                                        {
                                            var pcData = checkPlaces.Find(_ => string.IsNullOrEmpty(_.CheckMind));
                                            if (pcData != null)
                                            {
                                                var aa = CalcYinHuanNum(pcData);
                                                residentDict[area.Key].Fxyhcount += aa.Item1;

                                                var findCheckDatas = checkPlaces.Where(_ => _.Id != pcData.Id)
                                                    .OrderBy(_ => _.CreateOn).ToList();
                                                for (int i = 0; i < findCheckDatas.Count; i++)
                                                {
                                                    if (i == 0)
                                                    {
                                                        var rst = CalcPlaceResident(pcData, findCheckDatas[i]);
                                                        residentDict[area.Key].Fxyhcount += rst.Fxyhcount;
                                                        residentDict[area.Key].Xcyhcount += rst.Xcyhcount;
                                                    }
                                                    else
                                                    {
                                                        var rst = CalcPlaceResident(findCheckDatas[i - 1], findCheckDatas[i]);
                                                        residentDict[area.Key].Fxyhcount += rst.Fxyhcount;
                                                        residentDict[area.Key].Xcyhcount += rst.Xcyhcount;
                                                    }
                                                }
                                            }
                                        }
                                        else
                                        {
                                            var aa = CalcYinHuanNum(place);
                                            residentDict[area.Key].Fxyhcount += aa.Item1;
                                            //commercialDict[area.Key].Fxyhcount += (int)place.NumyhwtXf;
                                        }
                                    }
                                }
                            }
                        }
                        else// 不存在核查数据
                        {
                            foreach (var area in dictHouse)
                            {
                                var value = new PcYhStatDto { Code = area.Key };
                                residentDict.Add(area.Key, value);
                                var houseIds = area.Value.Where(_ => pHIds.Contains(_));

                                foreach (var hid in houseIds)
                                {
                                    var findPlaceResidentDatas = placeResidentDatas.FindAll(_ => _.HouseId == hid)?.ToList();
                                    foreach (var place in findPlaceResidentDatas)
                                    {
                                        dictHouse_count_yh_cs.Add(place.Id, (int)place.NumyhwtXf);
                                        cs_count++;
                                        if (place.Pczt == 21)
                                        {
                                            cs_count_yh++;
                                        }
                                        var aa = CalcYinHuanNum(place);
                                        residentDict[area.Key].Fxyhcount += aa.Item1;
                                        //commercialDict[area.Key].Fxyhcount += (int)place.NumyhwtXf;
                                    }
                                }
                            }
                        }
                    }
                    #endregion




                    #region 商业




                    List<ZmPlaceCommercial> placeCommercialDatas = new List<ZmPlaceCommercial>();
                    List<ZmPlaceCommercialCheck> placeCommercialCheckDatas = new List<ZmPlaceCommercialCheck>();


                    //消防数据时间分割数据池统计
                    if (dfg == "1")
                    {
                        placeCommercialDatas = _placeCommercialRepository
                        .Query(_ => _.Type == 0 && _.IsDelete != 1
                        && _.Pczt != null && _.Pczt > 1 && (_.CreateOn >= xf_fgcount || _.ModifiedOn >= xf_fgcount))?.ToList();
                        placeCommercialCheckDatas = _placeCommercialCheckRepository.Query(_ => _.Type == 0)?.ToList();//核查数据
                    }
                    else if (dfg == "0")
                    {
                        placeCommercialDatas = _placeCommercialRepository
                        .Query(_ => _.Type == 0 && _.IsDelete != 1
                        && _.Pczt != null && _.Pczt > 1 && (_.CreateOn < xf_fgcount || _.ModifiedOn < xf_fgcount))?.ToList(); // 已排查的居住消防场所数据
                        placeCommercialCheckDatas = _placeCommercialCheckRepository.Query(_ => _.Type == 0)?.ToList();//核查数据
                    }
                    else
                    {
                        placeCommercialDatas = _placeCommercialRepository
                            .Query(_ => _.Type == 0 && _.IsDelete != 1
                            && _.Pczt != null && _.Pczt > 1)?.ToList(); // 已排查的商业消防场所数据

                        placeCommercialCheckDatas = _placeCommercialCheckRepository
                        .Query(_ => _.Type == 0)?.ToList();//核查数据

                    }

                    if (placeCommercialDatas != null && placeCommercialDatas.Count > 0)
                    {
                        var pHIds = placeCommercialDatas.Select(_ => _.HouseId).Distinct().ToList();

                        if (placeCommercialCheckDatas != null && placeCommercialCheckDatas.Count > 0) // 存在核查数据
                        {
                            foreach (var area in dictHouse)
                            {
                                var value = new PcYhStatDto { Code = area.Key };
                                commercialDict.Add(area.Key, value);
                                var houseIds = area.Value.Where(_ => pHIds.Contains(_));

                                foreach (var hid in houseIds)
                                {
                                    var findPlaceDatas = placeCommercialDatas.FindAll(_ => _.HouseId == hid)?.ToList();
                                    foreach (var place in findPlaceDatas)
                                    {
                                        cs_count++;
                                        if (place.Pczt == 21)
                                        {
                                            cs_count_yh++;
                                        }
                                        Xcyhcount += (int)place.NumyhwtXf;
                                        var checkPlaces = placeCommercialCheckDatas
                                            .FindAll(_ => _.PlaceId == place.Id)?.OrderBy(_ => _.CreateOn)?.ToList();
                                        if (checkPlaces != null && checkPlaces.Count > 0) // 存在此场所的核查数据
                                        {
                                            var pcData = checkPlaces.Find(_ => string.IsNullOrEmpty(_.CheckMind));
                                            if (pcData != null)
                                            {
                                                var aa = CalcYinHuanNum(pcData);
                                                commercialDict[area.Key].Fxyhcount += aa.Item1;

                                                var findCheckDatas = checkPlaces.Where(_ => _.Id != pcData.Id)
                                                    .OrderBy(_ => _.CreateOn).ToList();
                                                for (int i = 0; i < findCheckDatas.Count; i++)
                                                {
                                                    if (i == 0)
                                                    {
                                                        var rst = CalcPlaceCommercial(pcData, findCheckDatas[i]);
                                                        commercialDict[area.Key].Fxyhcount += rst.Fxyhcount;
                                                        commercialDict[area.Key].Xcyhcount += rst.Xcyhcount;
                                                    }
                                                    else
                                                    {
                                                        var rst = CalcPlaceCommercial(findCheckDatas[i - 1], findCheckDatas[i]);
                                                        commercialDict[area.Key].Fxyhcount += rst.Fxyhcount;
                                                        commercialDict[area.Key].Xcyhcount += rst.Xcyhcount;
                                                    }
                                                }
                                            }
                                        }
                                        else
                                        {
                                            //var aa = CalcYinHuanNum(place);
                                            //commercialDict[area.Key].Fxyhcount += aa.Item1;
                                            //commercialDict[area.Key].Fxyhcount += (int)place.NumyhwtXf;
                                        }
                                    }
                                }
                            }
                        }
                        else// 不存在核查数据
                        {
                            foreach (var area in dictHouse)
                            {
                                var value = new PcYhStatDto { Code = area.Key };
                                commercialDict.Add(area.Key, value);
                                var houseIds = area.Value.Where(_ => pHIds.Contains(_));

                                foreach (var hid in houseIds)
                                {
                                    var findPlaceDatas = placeCommercialDatas.FindAll(_ => _.HouseId == hid)?.ToList();
                                    foreach (var place in findPlaceDatas)
                                    {
                                        cs_count++;
                                        if (place.Pczt == 21)
                                        {
                                            cs_count_yh++;
                                        }
                                        Xcyhcount += (int)place.NumyhwtXf;
                                        //var aa = CalcYinHuanNum(place);
                                        //commercialDict[area.Key].Fxyhcount += aa.Item1;
                                        //commercialDict[area.Key].Fxyhcount += (int)place.NumyhwtXf;
                                    }
                                }
                            }
                        }
                    }
                    #endregion
                    var totalDict = new Dictionary<string, PcYhStatDto>();
                    foreach (var item in residentDict)
                    {

                        if (totalDict.ContainsKey(item.Key))
                        {
                            totalDict[item.Key].Fxyhcount += item.Value.Fxyhcount;
                            totalDict[item.Key].Xcyhcount += item.Value.Xcyhcount;

                        }
                        else
                        {
                            var value = new PcYhStatDto { Code = item.Key };
                            value.Fxyhcount = item.Value.Fxyhcount;
                            value.Xcyhcount = item.Value.Xcyhcount;

                            //已排查-房屋
                            var getYpcfwcount = dictHouse_count.Where(q => q.Key == item.Key).FirstOrDefault();
                            value.Ypcfwcount = getYpcfwcount.Value;
                            //消隐-房屋
                            getYpcfwcount = dictHouse_count_yh.Where(q => q.Key == item.Key).FirstOrDefault();
                            value.Ypcfwcount_yh = getYpcfwcount.Value;

                            string sqlxfyh = "select  COALESCE((select sum(aa.numyhwt_xf) from zm_place_commercial  aa,zm_buildings_layer  bb  where aa.id in (SELECT id FROM zm_buildings where check107=1) and  aa.house_id=bb.ID and bb.village='" + item.Key + "'and  aa.is_delete IS NULL AND aa.type = 0 AND aa.pczt > 1 ),0)+ COALESCE((select sum(aa.numyhwt_xf) from zm_place_resident  aa,zm_buildings_layer  bb  where  aa.house_id=bb.ID and bb.village='" + item.Key + "'and  aa.is_delete IS NULL AND aa.type = 0 AND aa.pczt > 1),0) dd ";

                            if (dfg == "1")
                            {
                                sqlxfyh = "select  COALESCE((select sum(aa.numyhwt_xf) from zm_place_commercial  aa,zm_buildings_layer  bb  where  aa.id in (SELECT id FROM zm_buildings where check107=1) and aa.house_id=bb.ID and bb.village='" + item.Key + "'and  aa.is_delete IS NULL AND aa.type = 0 AND aa.pczt > 1 and (aa.create_on >='" + xf_fgcount + "' or aa.modified_on >='" + xf_fgcount + "') ),0)+ COALESCE((select sum(aa.numyhwt_xf) from zm_place_resident  aa,zm_buildings_layer  bb  where  aa.house_id=bb.ID and bb.village='" + item.Key + "'and  aa.is_delete IS NULL AND aa.type = 0 AND aa.pczt > 1 and (aa.create_on >='" + xf_fgcount + "' or aa.modified_on >='" + xf_fgcount + "')),0) dd ";

                            }
                            else if (dfg == "0")
                            {
                                sqlxfyh = "select  COALESCE((select sum(aa.numyhwt_xf) from zm_place_commercial  aa,zm_buildings_layer  bb  where aa.id in (SELECT id FROM zm_buildings where check107=1) and  aa.house_id=bb.ID and bb.village='" + item.Key + "'and  aa.is_delete IS NULL AND aa.type = 0 AND aa.pczt > 1 and (aa.create_on<'" + xf_fgcount + "' or aa.modified_on<'" + xf_fgcount + "') ),0)+ COALESCE((select sum(aa.numyhwt_xf) from zm_place_resident  aa,zm_buildings_layer  bb  where  aa.house_id=bb.ID and bb.village='" + item.Key + "'and  aa.is_delete IS NULL AND aa.type = 0 AND aa.pczt > 1 and (aa.create_on<'" + xf_fgcount + "' or aa.modified_on<'" + xf_fgcount + "')),0) dd ";
                            }
                            else
                            {
                                sqlxfyh = "select  COALESCE((select sum(aa.numyhwt_xf) from zm_place_commercial  aa,zm_buildings_layer  bb  where  aa.id in (SELECT id FROM zm_buildings where check107=1) and aa.house_id=bb.ID and bb.village='" + item.Key + "'and  aa.is_delete IS NULL AND aa.type = 0 AND aa.pczt > 1 ),0)+ COALESCE((select sum(aa.numyhwt_xf) from zm_place_resident  aa,zm_buildings_layer  bb  where  aa.house_id=bb.ID and bb.village='" + item.Key + "'and  aa.is_delete IS NULL AND aa.type = 0 AND aa.pczt > 1),0) dd ";
                            }

                            var xfyh_count = _dbContext.Database.GetDbConnection().Query<long>(sqlxfyh).FirstOrDefault();
                            value.Syyhcount_pc = xfyh_count;


                            totalDict.Add(item.Key, value);
                        }
                    }
                    foreach (var item in commercialDict)
                    {
                        if (totalDict.ContainsKey(item.Key))
                        {
                            totalDict[item.Key].Fxyhcount += item.Value.Fxyhcount;
                            totalDict[item.Key].Xcyhcount += item.Value.Xcyhcount;
                        }
                        else
                        {
                            var value = new PcYhStatDto { Code = item.Key };
                            value.Fxyhcount = item.Value.Fxyhcount;
                            value.Xcyhcount = item.Value.Xcyhcount;
                            totalDict.Add(item.Key, value);
                        }
                    }


                    foreach (var item in totalDict)
                    {
                        item.Value.Syyhcount = item.Value.Fxyhcount - item.Value.Xcyhcount;
                        datas.Add(item.Value);
                    }

                    var departments = _cache.Get("departments") as List<ZmDepartment>;
                    if (null == departments)
                    {
                        departments = _departmentRepository.Query().ToList();
                        _cache.Set("departments", departments);
                    }

                    foreach (var item in datas)
                    {

                        var findpidvillage = departments.Find(_ => _.RegionCode == item.Code);


                        if (findpidvillage != null)
                        {
                            item.village = findpidvillage?.RegionName;
                            item.villageCode = findpidvillage?.RegionCode;

                            var find1Town = departments.Find(_ => _.RegionCode == findpidvillage.ParentId);
                            if (find1Town != null)
                            {
                                item.Town = find1Town?.RegionName;
                                item.TownCode = find1Town?.RegionCode;

                                var findpidxzq = departments.Find(_ => _.RegionCode == find1Town.ParentId);
                                if (findpidxzq != null)
                                {
                                    item.Name = findpidxzq?.RegionName;
                                    item.Code = findpidxzq?.RegionCode;
                                }
                            }

                        }


                    }
                    #endregion
                    _cache.Set("PcYhStatData107", datas);
                }
                else if (dfg == "0")
                {
                    string strsql = "SELECT xzq as name ,xzqcode as code,town,towncode, village, villagecode, fxyhcount, xcyhcount, syyhcount, ypcfwcount, ypcfwcount_yh FROM zm_xf_xy_count314 where villagecode in (select region_code from zm_department where check107=1 and regional_level='7') order by villagecode asc";
                    var datasold777 = _dbContext.Database.GetDbConnection().Query<PcYhStatDto>(strsql).ToList();
                    datas = datasold777;
                    _cache.Set("PcYhStatData106", datasold777);
                }
                if (dfg == "1")
                {
                    string del = "delete from zm_xf_xy_count316 where check107=1 ";
                    var dela = _dbContext.Database.ExecuteSqlRaw(del);
                    foreach (var v in datas)
                    {
                        var d = Guid.NewGuid().ToString();
                        string dd = DateTime.Now.ToString();
                        string sql = " INSERT INTO public.zm_xf_xy_count316 " +
                            "(id, xzq, xzqcode, town, towncode, village, villagecode, fxyhcount, xcyhcount, syyhcount, ypcfwcount, ypcfwcount_yh, createdate,check107) VALUES" +
                            " ('" + d + "', '" + v.Name + "', '" + v.Code + "', '" + v.Town + "', '" + v.TownCode + "', '" + v.village + "', '" + v.villageCode + "', '" + v.Fxyhcount + "', '" + v.Xcyhcount + "', '" + v.Syyhcount + "', '" + v.Ypcfwcount + "', '" + v.Ypcfwcount_yh + "',  '" + dd + "',1);";
                        var num = _dbContext.Database.ExecuteSqlRaw(sql);
                    }
                    _cache.Set("Isloading107", false);
                }

                if (dfg == "1")
                {
                    string sql = @"SELECT
cz.region_code AS villageCode,
jz.region_code AS TownCode,
qx.region_code Code,
cz.region_name AS village,
jz.region_name AS town,
qx.region_name as name,
COALESCE(to_char(createdate, 'YYYY-MM-DD HH24:MI:SS'), (SELECT to_char(createdate, 'YYYY-MM-DD HH24:MI:SS') FROM zm_xf_xy_count316 where check107=1 LIMIT 1)) AS createdate,
COALESCE(fxyhcount, '0') AS fxyhcount,
COALESCE(xcyhcount, '0') AS xcyhcount,
COALESCE(syyhcount, '0') AS syyhcount,
COALESCE(ypcfwcount, '0') AS ypcfwcount,
COALESCE(ypcfwcount_yh, '0') AS ypcfwcount_yh
FROM
zm_department cz
LEFT JOIN zm_department jz
ON jz.region_code = cz.parent_id
LEFT JOIN zm_department qx
ON jz.parent_id = qx.region_code
LEFT JOIN zm_xf_xy_count316
ON cz.region_code = zm_xf_xy_count316.villagecode
AND zm_xf_xy_count316.check107 = 1
WHERE
cz.check107 = 1
AND cz.regional_level = '7'
ORDER BY
cz.region_code";
                    datas = _dbContext.Database.GetDbConnection().Query<PcYhStatDto>(sql).ToList();
                    // cacheData = _cache.Get<List<PcYhStatDto>>("PcYhStatData777");
                }
                else if (dfg == "0")
                {
                    string sql = @"SELECT
cz.region_code AS villageCode,
jz.region_code AS TownCode,
qx.region_code Code,
cz.region_name AS village,
jz.region_name AS town,
qx.region_name as name,
COALESCE(to_char(createdate, 'YYYY-MM-DD HH24:MI:SS'), (SELECT to_char(createdate, 'YYYY-MM-DD HH24:MI:SS') FROM zm_xf_xy_count314 where check107=1 LIMIT 1)) AS createdate,
COALESCE(fxyhcount, '0') AS fxyhcount,
COALESCE(xcyhcount, '0') AS xcyhcount,
COALESCE(syyhcount, '0') AS syyhcount,
COALESCE(ypcfwcount, '0') AS ypcfwcount,
COALESCE(ypcfwcount_yh, '0') AS ypcfwcount_yh
FROM
zm_department cz
LEFT JOIN zm_department jz
ON jz.region_code = cz.parent_id
LEFT JOIN zm_department qx
ON jz.parent_id = qx.region_code
LEFT JOIN zm_xf_xy_count314
ON cz.region_code = zm_xf_xy_count314.villagecode

WHERE
cz.check107 = 1
AND cz.regional_level = '7'
ORDER BY
cz.region_code";
                    datas = _dbContext.Database.GetDbConnection().Query<PcYhStatDto>(sql).ToList();
                }
                //市级管理员只看到区数据
                if (user.RoleType == 2 && user.Group == 2)
                {
                    JObject obj = JObject.Parse(user.District);
                    string RegionCode = (string)obj["RegionCode"]; // "John"
                    datas = datas.GroupBy(o => o.Code)
                   .Select(g => new PcYhStatDto
                   {
                       Code = g.Key,
                       Syyhcount = g.Sum(o => o.Syyhcount),
                       Syyhcount_pc = g.Sum(o => o.Syyhcount_pc),
                       Name = g.Max(o => o.Name),
                       Xcyhcount = g.Sum(o => o.Xcyhcount),
                       Ypccount_ch = g.Sum(o => o.Ypccount_ch),
                       Ypccount_ch_yh = g.Sum(o => o.Ypccount_ch_yh),
                       Ypcfwcount = g.Sum(o => o.Ypcfwcount),
                       Ypcfwcount_yh = g.Sum(o => o.Ypcfwcount_yh),
                       Fxyhcount = g.Sum(o => o.Fxyhcount),
                       createdate = g.Max(o => o.createdate)
                   }).ToList();
                    datas = datas.OrderBy(c => c.Code).ToList();
                    return new RespRet<List<PcYhStatDto>>(true, "Ok", datas);
                }
                else if (user.RoleType == 3 && user.Group == 2)
                {
                    JObject obj = JObject.Parse(user.District);
                    string RegionCode = (string)obj["RegionCode"]; // "John"
                    datas = datas.Where(c => c.Code == RegionCode).ToList();
                    datas = datas.GroupBy(o => o.TownCode)
                  .Select(g => new PcYhStatDto
                  {
                      Code = g.Key,
                      Syyhcount = g.Sum(o => o.Syyhcount),
                      Syyhcount_pc = g.Sum(o => o.Syyhcount_pc),
                      Name = g.Max(o => o.Name),
                      Xcyhcount = g.Sum(o => o.Xcyhcount),
                      Ypccount_ch = g.Sum(o => o.Ypccount_ch),
                      Ypccount_ch_yh = g.Sum(o => o.Ypccount_ch_yh),
                      Ypcfwcount = g.Sum(o => o.Ypcfwcount),
                      Ypcfwcount_yh = g.Sum(o => o.Ypcfwcount_yh),
                      Fxyhcount = g.Sum(o => o.Fxyhcount),
                      createdate = g.Max(o => o.createdate),
                      Town = g.Max(o => o.Town),
                      TownCode = g.Max(o => o.TownCode)
                  }).ToList();
                }
                else if (user.RoleType == 4 && user.Group == 2)
                {
                    JObject obj = JObject.Parse(user.District);
                    string RegionCode = (string)obj["RegionCode"]; // "John"
                    datas = datas.Where(c => c.TownCode == RegionCode).ToList();
                    datas = datas.GroupBy(o => o.villageCode)
                  .Select(g => new PcYhStatDto
                  {
                      Code = g.Key,
                      Syyhcount = g.Sum(o => o.Syyhcount),
                      Syyhcount_pc = g.Sum(o => o.Syyhcount_pc),
                      Name = g.Max(o => o.Name),
                      Xcyhcount = g.Sum(o => o.Xcyhcount),
                      Ypccount_ch = g.Sum(o => o.Ypccount_ch),
                      Ypccount_ch_yh = g.Sum(o => o.Ypccount_ch_yh),
                      Ypcfwcount = g.Sum(o => o.Ypcfwcount),
                      Ypcfwcount_yh = g.Sum(o => o.Ypcfwcount_yh),
                      Fxyhcount = g.Sum(o => o.Fxyhcount),
                      createdate = g.Max(o => o.createdate),
                      Town = g.Max(o => o.Town),
                      village = g.Max(o => o.village),
                      villageCode = g.Max(o => o.villageCode),
                      TownCode = g.Max(o => o.TownCode)
                  }).ToList();
                    datas = datas.OrderBy(c => c.Code).ToList();
                    return new RespRet<List<PcYhStatDto>>(true, "Ok", datas);
                }
                datas = datas.OrderBy(c => c.Code).ToList();
                return new RespRet<List<PcYhStatDto>>(true, "Ok", datas);

            }
        }



    }
}
